SumProduct (I think!) question

E

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
 
B

Bernd P

Hello Emma,

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

Regards,
Bernd
 
M

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
 
J

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top