Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: Sumif function based on date range

  1. #1
    Join Date
    20th July 2004
    Posts
    20

    Sumif function based on date range

    I have a list of records say with an amount and a date. How can I analyse the list based on the date range i.e. all records between say date 1 and date 2 total x. I would like the total to be returned to a single cell

    Thanks,

    Brian.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    Assuming the value is in A1:A100 and the dates are in B1:B100

    =SUMPRODUCT((B1:B100>=DATEVALUE("1/1/2004"))*(B1:B100<=DATEVALUE("31/1/2004")),A1:A100)

    will return the sum of the values fro January 2004
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713
    Hi Brian

    Here is one way

    =SUMPRODUCT(($A$2:$A$100>=D1)*($A$2:$A$100<=D2)*($B$2:$B$100))

    Where $A$1:$A$100 has the dates and $B$1:$B$100 the numbers to SUM. D1= date1 and D2 = date2

    Or, use the more efficient DSUM like

    =DSUM($A$1:$B$100,2,$E$1:$F$2)

    Where A1 is the Date heading
    E1 and F1 has the exact same heading as A1
    E2 has >=11/11/04
    F2 has <=12/12/04

  4. #4
    Join Date
    20th July 2004
    Posts
    20

    Date imported with ' before it - Excel can't read it

    I have dates imported in this format say '01/01/2004
    Excel can't then read the cell. Ive tried replacing the ' but Excel can't dind it. Should be a simple problem. Please help!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    Those are not dates & need converting first.

    Try running this code on the range containing the text-dates

    VB:
    Sub Convert_To_Date() 
         'converts a range of text dates to excel dates
        Dim cdata As Range, cell As Range 
        Set cdata = Application. _ 
        InputBox("Select Your Range to Convert", Type:=8) 
        For Each cell In cdata.Cells 
            cell.Value = DateValue(cell.Value) 
        Next cell 
    End Sub 
    
    
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713
    You could also use

    =MID(A1,1,256)+0

    then Copy and Edit>Paste Special - Values over the top of the orginals. Then format as required.

  7. #7
    Join Date
    20th July 2004
    Posts
    20

    Date range with ' before it

    I'm not familiar with code at all and have no idea how to do it. Is there anyway in Excel that I can conver it. When I go into the individual cell and just delete the ' Excel can read it. I just need a quick way of doing this for a few thousand records. A macro perhaps? I don't know.

    Thanks,

    Brian.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713
    Brian, Will's method is a macro

    Push Alt+F11 and go to Insert>Module and paste in the code. Now go back to Excel and push Alt+F8 and selected Convert_To_Date and click Run

    No trying to be smart, but I would suggest changing your Assumed Experience: down from Well above average

  9. #9
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    That's what I gave you.

    OK, press ALT+F11 from your worksheet

    This opens the Visual Basic Editor.

    Click Insert|Module

    Paste the code I gave you in the new module.

    Then you can just run the macro from your worksheet using Tools|Macros.

    Or, use Dave's formula based suggestion...
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  10. #10
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    Try the macro or this

    =DATEVALUE(CLEAN(A1))
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. SUMIF function within a SUMIF function??? [SOLVED] - Excel Help & VBA ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  2. Replies: 2
    Last Post: July 1st, 2008, 09:40
  3. Sumif Based On Range Of Numbers
    By Gon in forum EXCEL HELP
    Replies: 8
    Last Post: September 27th, 2006, 16:38
  4. SUMIF based on date ranges
    By safaller in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2006, 00:53
  5. SUMIF Function based on Row selected
    By mattalin in forum EXCEL HELP
    Replies: 5
    Last Post: April 27th, 2006, 02:49

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno