Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Sumproduct with a vLookup, multiple criteria | Excel Worksheet Functions | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions |