# SUMIF comparing dates

#### ski

##### New Member
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!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### MrExcel MVP
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.

#### Yogi Anand

##### MrExcel MVP
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
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
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

Replies
3
Views
85
Replies
0
Views
41
Replies
5
Views
339
Replies
17
Views
175
Replies
13
Views
161

1,127,323
Messages
5,624,014
Members
416,006
Latest member
PCaffrey

### 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?

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