sumproduct formula (multiple criteria)

[Inter]

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

[Stefi]

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

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Inter]

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

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Inter]

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

[paul]

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


--
paul
[email protected]
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
>

[Inter]

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

[Ardus Petus]

=SUMPRODUCT(--(ISERROR(MATCH(C1:C10,{"Brand A","Brand
B"},0))),--(ISERROR(MATCH(B1:B10,{"France","Italy"},0))),A1:A10)
"Inter" <[email protected]> a �crit dans le message de news:
[email protected]...
> 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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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