Help with SUMIF function

C

Confused Dan

Hi,

I'm trying to add up a range of cells based on two IF statements, but with
no luck :(

What I was trying to do was:

=Sum(If (G3:G2224,"Home")*(M3:M2224,"Blue"),P3:p2224)

This doesn't call up the result that i want,which is the sum of the P cells
if range G = home and range M = blue.

Can anyone suggest a work around or point out to me what I am doing wrong?

All help would be very much appreciated!
 
B

Bob Phillips

Your formula can work with a couple of extra brackets, a testing operator
and array entered

=SUM(IF((G3:G2224="Home")*(M3:M2224="Blue"),P3:p2224))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Because you are not using SUMIF but SUM(IF, you cannot use the same
construct, so the conditional test has to be explicit.

Or you can use Roger's SUMPRODUCT solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
A

Art MacNeil

Damn. I didn't realize Excel could/would do that.

Thanks.


Bob Phillips said:
Your formula can work with a couple of extra brackets, a testing operator
and array entered

=SUM(IF((G3:G2224="Home")*(M3:M2224="Blue"),P3:p2224))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

Because you are not using SUMIF but SUM(IF, you cannot use the same
construct, so the conditional test has to be explicit.

Or you can use Roger's SUMPRODUCT solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Top