nesting sumif functions

  • Thread starter Thread starter Macil
  • Start date Start date
M

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
 
Give this formula a try...

=SUMPRODUCT(($A$5:$A$63="jones")*($B$5:$B$63="a")*C$5:C$63)
 
SUMPRODUCT is the way to go.

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

Hope this helps.
 
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 said:
try this

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

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