SumProduct (I think!) question

[Emma C]

Hi

Can anyone help with a probably simple query

I have 3 columns of data (example below) and I need to do a sum of cost if
the value in column A equals Saving and column B DOES NOT EQUAL Blue:

e.g:

Column A Column B Amount
-------------------------------------------------------------------
Saving Red £25.00
Saving Blue £10.00
Saving Green £10.00
Cost Red £10.00
Cost Green £10.00
Saving Yellow £10.00

So the answer in this case would be £45.00 but I have no idea how to do the
formula to include the "does not equal" part.

All help gratefully received!

Emma

 

[Bernd P]

Hello Emma,

=SUMPRODUCT(--(A1:A99="Saving"),--(B1:B99<>"Blue"),C1:C99)

Regards,
Bernd

 

[Jim Thomlinson]

You are correct with sumproduct

=SUMPRODUCT(--($A$2:$A$10="Savings"), --($B$2:$B$10<>"Blue"), $C$2:$C$10)

Here is a link.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Mattlynn via OfficeKB.com]

you could use a subtotal formula based on column C
=SUBTOTAL(9,C2:C39) Change range to suit amount range
and then auto filter on column A to "saving", and then on column B autofilter
customise to say does not contaon "blue" This gives you the 45 also.
Matt

 

[Emma C]

Thanks Jim that worked brilliantly!

You are correct with sumproduct

=SUMPRODUCT(--($A$2:$A$10="Savings"), --($B$2:$B$10<>"Blue"), $C$2:$C$10)

Here is a link.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Jim Thomlinson]

I don't normally plug a site but Bob who maintains that site has added an
option to buy him a beer. There is more than enough content there to warrant
a beer. It is your call but I know that I bought him one.

Bob - I paid for the beer. Use the next donation to pay for the taxi...