Help with SUMIF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Confused Dan - 20 Sep 2006 09:55 GMT

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!

Signature

Regards,

Dan

Reply to this Message

Roger Govier - 20 Sep 2006 10:26 GMT

Hi

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

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 14 lines]
>
> All help would be very much appreciated!

Reply to this Message

Confused Dan - 20 Sep 2006 10:43 GMT

Thanks very much for your help! It worked a treat! :-)

Signature

Regards,

Dan

> Hi
>
[quoted text clipped - 19 lines]
> >
> > All help would be very much appreciated!

Reply to this Message

Bob Phillips - 20 Sep 2006 10:33 GMT

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.

Signature

HTH

Bob Phillips

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

> Hi,
>
[quoted text clipped - 11 lines]
>
> All help would be very much appreciated!

Reply to this Message

Art MacNeil - 20 Sep 2006 15:40 GMT

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
[quoted text clipped - 28 lines]
>>
>> All help would be very much appreciated!

Reply to this Message

Rate this thread: