Help with SUMIF function

[Guest]

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"),P32224)

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!

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((G3:G2224,"Home")*(M3:M2224,"Blue")*P32224)

 

[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"),P32224))

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)

 

[Guest]

Thanks very much for your help! It worked a treat!
--
Regards,

Dan

Hi

Try
=SUMPRODUCT((G3:G2224,"Home")*(M3:M2224,"Blue")*P32224)

 

[Art MacNeil]

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

Thanks.

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

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

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)