SUMIFS returns VALUE!

**mogdog1981** · 06-04-2010, 08:10 AM

Hi,

I'm using the SUMIFS function and am finding that using the following code always returns a VALUE! error, even when I use the Math and Trig wizard.

=SUMIFS(B3:C5,B3:B5,"north",C3:C5,1000)

Please can you help?

Thanks

Mike

**daddylonglegs** · 06-04-2010, 08:23 AM

With SUMIFS all ranges need to be the same size (and shape). B3:C5 is double the size of the other two ranges so that won't work. Can you explain what you want to do? If B3:B5 might contain "North" then surely sum range should be C3:C5?, i.e.

=SUMIFS(C3:C5,B3:B5,"north",C3:C5,1000)

**ASM Jasim Uddin** · 06-04-2010, 11:42 AM

Originally Posted by mogdog1981

Hi,

I'm using the SUMIFS function and am finding that using the following code always returns a VALUE! error, even when I use the Math and Trig wizard.

=SUMIFS(B3:C5,B3:B5,"north",C3:C5,1000)

Please can you help?

Thanks

Mike

Ref : Sumifs()

What i have understood is your formula code contains some error. your formula might be =SUMIFS(C3:C5,B3:B5,"north",C3:C5,1000). Because you are adding only the values contained in C3:C5 not in B3:C5.

Jasim

**mogdog1981** · 06-08-2010, 06:51 AM

Thanks guys, that has solved the problem. I'm quite new to this (as you may have guessed!), want i don't understand is why SUMIFS only works if the "north" arguement looks at both ranges, (C3:C5, B3:B5) while the "1000" arguement looks at just the one range (C3:C5), why is that?

**teylyn** · 06-08-2010, 06:58 AM

mogdog,

want i don't understand is why SUMIFS only works if the "north" arguement looks at both ranges, (C3:C5, B3:B5)

It does not look at both ranges for the "north" argument. The syntax for Sumifs is

=Sumifs(sum_range, criteria_range1, criteria1, criteria_rangen, criterian)

So, the first argument is not where it looks for any comparison but for the values to be summed if the ensuing criteria are met in their respective ranges.

**Izakvdm** · 02-03-2011, 03:09 AM

I have a different problem ... or maybe not so different.
I want to check horizontally and vertically and then sum.

Example of how I want it to work with the formula being in E2:E4
A B C D E
1 true true false Total
2 true 1 1 1 2
3 true 2 2 2 4
4 false 3 3 3 0

=SUMIFS(B2:D2,B1:D1,TRUE,A2:A4,TRUE)

This however returns the dreaded #VALUE! error

Any ideas will be appreciated!!

**teylyn** · 02-03-2011, 03:38 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.