# 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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

##### 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
163
Replies
2
Views
110
Replies
0
Views
134
Replies
3
Views
187
Replies
7
Views
173

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,938
Messages
5,834,487
Members
430,289
Latest member
Tphil413

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

### Which adblocker are you using?

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

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