Excel Sum if using a Date Range

[cgilmore]

Our purchasing dept. has a huge spreadsheet. They have a column for budget 2005 and a column for expenditures 2005. They want to sum the expenditures 2005 column where the date range column is >=1/1/2005 and <=1/31/2005. I typed just a small sample to see if I could get the sumif to work for a specified date range.

checks date amount
15000 1/1/2005 5
16000 1/1/2005 5
16001 1/11/2005 5
16002 1/12/2005 5
16003 2/1/2005 10
16004 2/2/2005 10
16005 2/3/2005 10
16006 2/4/2005 10

The following formula works:
=SUMIF(B2:B9,"<=1/31/2005",C2:C9 I get 20 which is correct.
This will work for January, but in February I will not be able to do <=2/28/2005 because this will also include January. In February I must have >= 2/1/2005 and <=2/28/2005. Eventually I will need to do this for all the months in a huge spreadsheet. I tried changing the formula to
=SUMIF(B2:B9,">=1/1/2005*<=1/31/2005",C2:C9) but I get a 0
Then I changed the formula to
=SUMIF(B2:B9,">=datevalue(1/1/2005)*<=datevalue(1/31/2005)",C2:C9)
and again I got a 0. What am I doing wrong?

Thanks!

 

[xlbo]

Try some logic. If Jan is
=SUMIF(B2:B9,"<=1/31/2005",C2:C9)
then Feb is:
=SUMIF(B2:B9,"<=2/28/2005",C2:C9) - SUMIF(B2:B9,"<=1/31/2005",C2:C9)

or, to express the logic

Jan = Sum for Jan
Feb = Sum for YTD - Jan
March = Sum YTD - Sum YTD @ Feb
OR
March = Sum YTD - Feb - Jan
etc etc

If you have them in cells, then in A1 you could have:
=SUMIF(B2:B9,"<=1/31/2005",C2:C9)
In A2,
=SUMIF(B2:B9,"<=2/28/2005",C2:C9) - A1
In A3,
=SUMIF(B2:B9,"<=3/31/2005",C2:C9) - SUM(A1:A2)

etc etc

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

This is an ideal situation for database techniques.

Set up a criteria range that looks like this (I used F1:G2):
[tt]
date date
>=2/1/2005 <=2/28/2005
[/tt]
Then with your sample database in A1:C9 this formula produces the desired result (40):
[tt]
=DSUM($A$1:$C$9,3,$F$1:$G$2)
[/tt]
All you need to do is set up a separate critera range for each month and modify the formula accordingly.

See the help file for details. The column names must match. It is also better to name your ranges so that the formulas can look like this:
[tt]
=DSUM(DATABASE,3,JANUARY)
=DSUM(DATABASE,3,FEBRUARY)
etc.
[/tt]

 

[KenWright]

LOL - To me that table of data cries out for a Pivot table:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[cgilmore]

Thank you so much. You guys are the MEN!