SUMIF comparing dates

ski

New Member
Joined
Oct 27, 2002
Messages
8
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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

New Member
Joined
Oct 27, 2002
Messages
8
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top