sumproduct formula (multiple criteria)
 

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

sumproduct formula (multiple criteria)
 

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:

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

sumproduct formula (multiple criteria)
 

=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)

"Inter" wrote in message
...
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

sumproduct formula (multiple criteria)
 

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 formula (multiple criteria)
 

=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)

"Inter" wrote in message
...
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 formula (multiple criteria)
 

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

sumproduct formula (multiple criteria)
 

=SUMPRODUCT(--(Genius),--("Bob","Stefi"))?


--
paul

remove nospam for email addy!



"Inter" wrote:

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

sumproduct formula (multiple criteria)
 

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 formula (multiple criteria)
 

=SUMPRODUCT(--(ISERROR(MATCH(C1:C10,{"Brand A","Brand
B"},0))),--(ISERROR(MATCH(B1:B10,{"France","Italy"},0))),A1:A 10)
"Inter" a écrit dans le message de news:
...
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 formula (multiple criteria)
 

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)

"Inter" wrote in message
...
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 formula (multiple criteria)
 

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

"Bob Phillips" wrote:

=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)

"Inter" wrote in message
...
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 formula (multiple criteria)
 

=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)

"merry_fay" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"Inter" wrote in message
...
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