Sumif criteria problem

Goatboy

New Member
Joined
Oct 24, 2006
Messages
3
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.

Any help, advice or suggestions gladly received.

GB
 

Some videos you may like

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.

Goatboy

New Member
Joined
Oct 24, 2006
Messages
3
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

What cells does named range Year2 occupy?
 

Goatboy

New Member
Joined
Oct 24, 2006
Messages
3
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
Joined
Apr 19, 2005
Messages
23,707
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 :confused:
 

Watch MrExcel Video

Forum statistics

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