nesting sumif functions

[Macil]

can I nest a sumif function within a sumif function? i want to sum the data
with in column c if data in coulmn a ="a" and data in column b="b" Here is
what I tried to write
=sumif($a$5:$a$63,"jones",sumif($b$5:$b$63,"a",c$5:c$63) It won't work and
can not determine where problem is

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT(($A$5:$A$63="jones")*($B$5:$B$63="a")*C$5:C$63)

 

[John C]

SUMPRODUCT is the way to go.

=SUMPRODUCT(--(A2:A100="a"),--(B2:B100="c"),(C2:C100))

Hope this helps.

 

[~L]

One way is to use Sumproduct instead of sumif.

This would go:
=sumproduct(--($A$5:$A$63="jones"),--($B$5:$B$63="a"),--($C$5:$C$63))

The problem with your current formula is, the value being returned from the
second sumif is not a range where a range is expected by the first sumif.

 

[muddan madhu]

try this

=SUMPRODUCT(--(A5:A63&B5:B63="a"&"b")*(C5:C63))

 

[Harlan Grove]

try this

=SUMPRODUCT(--(A5:A63&B5:B63="a"&"b")*(C5:C63))

....

Or not.

If, for example, A5 contained "ab" while B5 were blank, A5&B5 would be
"ab", so C5 would be included in the sum even though A5 and B5 didn't
satisfy the actual criteria. ALWAYS safer to test
(A5:A6="a")*(B5:B6="b"), and there's not much recalculation
performance difference between that and your nonrobust approach.