SUMIF over multiple columns
 

is doing a SUMIF over multiple columns not allowed?

This formula works =SUMIF(C:C,J5,E:E)
but this does not: =SUMIF(C:C,J5,E:F)


--
psmith4497
------------------------------------------------------------------------
psmith4497's Profile: http://www.excelforum.com/member.php...o&userid=23597
View this thread: http://www.excelforum.com/showthread...hreadid=380121

Try...

=SUMPRODUCT((C1:C100=J5)*E1:F100)

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

psmith4497 Wrote:
is doing a SUMIF over multiple columns not allowed?

This formula works =SUMIF(C:C,J5,E:E)
but this does not: =SUMIF(C:C,J5,E:F)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=380121

=SUMIF(C:C,J5,E:E)+SUMIF(C:C,J5,f:f)



"psmith4497" wrote:


is doing a SUMIF over multiple columns not allowed?

This formula works =SUMIF(C:C,J5,E:E)
but this does not: =SUMIF(C:C,J5,E:F)


--
psmith4497
------------------------------------------------------------------------
psmith4497's Profile: http://www.excelforum.com/member.php...o&userid=23597
View this thread: http://www.excelforum.com/showthread...hreadid=380121

The problem is that the criteria array and the values array needs to be the
same size.
you could use
=SUMIF(C:C,J5,E:E)+SUMIF(C:C,J5,F:F)
or
=sumproduct(--(C1:C1000=J5),E1:E1000+F1:F1000)
Note you cannot use full columns in Sumproduct C1:C1000 is OK C:C is not
and the arrays need to be the same size in Sumproduct
(E1:E1000+F1:F1000 is really just a one thousand unit array, even though it
looks bigger)



"psmith4497" wrote:


is doing a SUMIF over multiple columns not allowed?

This formula works =SUMIF(C:C,J5,E:E)
but this does not: =SUMIF(C:C,J5,E:F)


--
psmith4497
------------------------------------------------------------------------
psmith4497's Profile: http://www.excelforum.com/member.php...o&userid=23597
View this thread: http://www.excelforum.com/showthread...hreadid=380121