Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Sumif function based on date range

1. Member
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. 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

3. 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. Member
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. 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

```

6. 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. Member
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. 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. 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...

10. Try the macro or this

=DATEVALUE(CLEAN(A1))

Users Browsing this Thread

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

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