sumif functions

[Macil]

Trying to nest sumif functions. want to sumif column h if column b contains
value "jones" and column c contains value "a". Wrote formula
=sumif($b$5:$b$63,"jones",(sumif($c$5:$c$63,"a",h$5:h$63) formula not
accepted.
If I can't nest sumif's is there a way to use the and function?

 

[Peo Sjoblom]

=SUMPRODUCT(--(B5:B63="Jones"),--(C5:C63="a"),H5:H63)


You can't use AND and you can't nest SUMIF but you can use the above, of
course if you have 2007 it has a new function called SUMIFS which can use
multiple criteria

--


Regards,


Peo Sjoblom

 

[Dave Peterson]

In xl2007, there's a new =sumifs() function.

In any version, you could use:
=sumproduct(--($b$5:$b$63="jones"),--($c$5:$c$63="a"),($h$4:$h$63))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Macil]

Thank you for your help - love excel, and always try to push it as far as it
can go. Now have another function to play with.

 

[Ashish Mathur]

Hi,

you can also use the following array formula (Ctrl+Shift+Enter)

=sum(if((range1=name)*(range2="a"),sum_range))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com