# Sumif criteria problem

#### Goatboy

##### New Member
I encountered the following problem yesterday on a cash report spreadsheet.

I’m trying to write a formula to sum all budget items for future years, I think the formula I need is;

=SUMIF(Year2,>Year1,E5:BF5)

(Where Year2 is a range of budget years, E5:BF5 the respective range of budget amounts to be summed and Year1 the year from which I want to calculate all budgets after).

The above formula returns the answer 0, but if I try

=SUMIF(Year2,Year1,E5:BF5)

It sums all budget items for Year1 correctly, and;

=SUMIF(Year2,Year1+1,E5:BF5)

Sums all budget items for the year after Year1, so I’m mystified as to what is wrong with the criteria “>Year1”.

In line with excel help I’ve tried entering the criteria in quote marks (the only thing I could see that was different in their example) and I’ve also tried separating the criteria with brackets.

GB

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Richard Schollar

##### MrExcel MVP
Hi GB

Welcome to the Board!

Did you try:

Code:
``=SUMIF(Year2,">" & Year1,E5:BF5)``

#### Goatboy

##### New Member
Thanks for the welcome.

Just tried that (and a couple of variations), no luck.

Other info that may be relevant is I'm using Excel 2003.

cheers

#### Richard Schollar

##### MrExcel MVP
Ok, try this array formula:

Code:
``=SUM(IF(Year2>Year1,E5:BF5))``

which you need to enter with Ctrl+Shift+Enter - Excel will surround with curly braces if it's worked like so:

Code:
``{=SUM(IF(Year2>Year1,E5:BF5))}``

#### Richard Schollar

##### MrExcel MVP

What cells does named range Year2 occupy?

#### Goatboy

##### New Member
What cells does named range Year2 occupy?

Year2 is E3:BF3,

That "=SUM(IF(Year2>Year1,E5:BF5))" has worked.

So thanks very much for that, it was driving me nuts.

GB

#### Richard Schollar

##### MrExcel MVP
I'm glad something worked for you!

I must admit I haven't been able to reproduce what you were seeing with the SUMIF. I guess it's just one of those things

Replies
13
Views
211
Replies
17
Views
384
Replies
3
Views
51
Replies
3
Views
71
Replies
3
Views
36

1,114,052
Messages
5,545,735
Members
410,703
Latest member
yaronjoseph