SUMIF comparing dates

[ski]

Hi all,
I tried to search for this but could not find anything. It seems like a simple problem, but for some reason doesn't work. I am using EXCEL 2000.

I have a table of data, organized by date (Column A). I need to sum up the values (Column B) associated with the dates occurring in the past 60 days (and other time periods). I tried this:

=sumif(A:A,">(today()-60)",B:B)

which seems logical to me, but returns a value of zero every time, for any time period. I've futzed around with a bunch of different versions, but can't get it to work. Can you compare dates? What am I missing?

Thanks!

 

[zacemmel]

Ski, maybe try a different approach. Check this out:

http://www.excelquestions.com/summary.php?idx=14

 

[PaddyD]

Hi - welcome to the board!

sumif (and countif) do not support the evaluation of further calculations in the criteria. The best you'll get is a reference to another cell tht contains the criteria.

sumproduct is an alternative:

=sumproduct((A1:A100>today()-60)*(B1:B100))

note that I have changed A:A to definite ranges of cells (a1:a100 etc). This is (a) good practise - you shouldn't perform more calculations / checks than you need, and (b) required for sumproduct.

paddy

 

[Yogi Anand]

Hi ski:

Your formula is almost right, except for the syntax in describing the criteria.

Picking up on the great suggestion by Paddy regarding delineating a specific range, I would change the formula to:

'=SUMIF(A1:A100,">"&(TODAY()-60),B1:B100)

Regards!
Yogi Anand

 

[ski]

Man you guys are quick. Thanks for the help - Yogi's solution was exactly what I needed. The "help" file wasn't too clear on teh syntax, even though it did have an example of a SUMIF based on a >. I'll have to research up on what exactly that & does.

Thanks again and hopefully I will be able to help someone else sometime.

Ski

 

[Yogi Anand]

On 2002-10-29 00:47, ski wrote:
Man you guys are quick. Thanks for the help - Yogi's solution was exactly what I needed. The "help" file wasn't too clear on teh syntax, even though it did have an example of a SUMIF based on a >. I'll have to research up on what exactly that & does.

Thanks again and hopefully I will be able to help someone else sometime.

Ski

Hi ski:

& is the concatenation operator (it is for combining or adding text entries, just like + is for adding numeric entries.

Regards!
Yogi Anand