sumproduct formula (multiple criteria)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I've got the sumproduct formula to work (thanks to you guys and girls) but
now what i want to do is sum cells according to 2 criteria ... but i want
both of those criteria to be this OR that OR something else.

To put it another way, i want to sum the number of downloads (in column A)
but only if column B contains either "France" OR "Italy" and column C
contains either "Brand A" OR "Brand B"

Many thanks
Stuart
 
Try this solution using a helper column:
Formula in helper column (say G), cell G2:
=AND(OR(B2="France",B2="Italy"),OR(C2="Brand A",C2="Brand B"))
and drag it down as required!
SUMPRODUCT formula:
=SUMPRODUCT(A2:A10,--G2:G10)

Regards,
Stefi


„Inter†ezt írta:
 
=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C10,{"Brand A","Brand
B"},0))),--(ISNUMBER(MATCH(B1:B10,{"France","Italy"},0))),A1:A10)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Thanks for both of these ... very helpful.

Follow up question though - how about if i want to sum the cells in column A
that have "France" OR "Italy" in column B but DO NOT have "Brand A" (i.e. do
have anything apart from "Brand A") in column C?

Cheers
Stuart
 
=SUMPRODUCT(--(C1:C10<>"Brand
A"),--(ISNUMBER(MATCH(B1:B10,{"France","Italy"},0))),A1:A10)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Bob and Stefi, you're geniuses! Or should that be genii? I don't know but i
can now do exactly what i wanted.

Many thanks
Stuart
 
Ok, final question (more out of interest than anything else) ... how would i
write a formula that would give me the sum of cells in column A that do not
have either "France" or "Italy" in column B and do not have either "Brand A"
or "Brand B" in column C?

I'd presume it's a hack of Bob's formula -
=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C10,{"Brand A","Brand
B"},0))),--(ISNUMBER(MATCH(B1:B10,{"France","Italy"},0))),A1:A10) - but i
can't see where you'd put the <> that works for a single specific criteria.

Cheers!
Stuart
 
=SUMPRODUCT(--(ISERROR(MATCH(C1:C10,{"Brand A","Brand
B"},0))),--(ISERROR(MATCH(B1:B10,{"France","Italy"},0))),A1:A10)
 
Because it is not testing for =, there is nothing to replace with <>. The
key is that it does its biz by testing the MATCH for a number
(ISNUMBER(MATCH(...). So you need to modify that, I would do it by testing
not a number

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(C1:C10,{"Brand A","Brand B"},0)))),
--(NOT(ISNUMBER(MATCH(B1:B10,{"France","Italy"},0)
))),A1:A10)


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Hi,

I've looked at this formula & can get it to work for my data if I type in
the criteria eg {"France","Italy"}, but what I really need is to be able to
use a cell reference eg {G5,G6} instead. How can I change the formula to do
this?

Thanks
Miranda
 
=SUMPRODUCT(--(C1:C10<>"Brand
A"),--(ISNUMBER(MATCH(B1:B10,G5:G6,0))),A1:A10)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top