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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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))}
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top