# SUMIFS returns VALUE!

1. ## SUMIFS returns VALUE!

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.

``Please Login or Register  to view this content.``

Thanks

Mike

2. ## Re: SUMIFS returns VALUE!

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)

3. ## Re: SUMIFS returns VALUE!

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.

``Please Login or Register  to view this content.``

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

4. ## Re: SUMIFS returns VALUE!

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?

5. ## Re: SUMIFS returns VALUE!

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.

6. ## Re: SUMIFS returns VALUE!

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!!

7. ## Re: SUMIFS returns VALUE!

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)