nesting sumif functions
 

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

nesting sumif functions
 

Give this formula a try...

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

--
Rick (MVP - Excel)


"Macil" wrote in message
...
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

nesting sumif functions
 

SUMPRODUCT is the way to go.

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

Hope this helps.
--
John C


"Macil" wrote:

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

nesting sumif functions
 

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.

"Macil" wrote:

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

nesting sumif functions
 

try this

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


On Oct 15, 11:36*pm, Macil wrote:
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

nesting sumif functions
 

muddan madhu wrote...
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.