Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
Hello
I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition is in column C OR Nutrition is in column D A B C D PwC New Nutition Writing Carer Return Writing Nutrition PwC New Writing Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
don't know how to achieve that with SUMPRODUCT
but you might try: =SUM(IF((B4:B87="New")*(A4:A87="PwC")*((C4:C87="Nu trition")+ (D4:D87="Nutrition")),1,)) insert it with CTRL+SHIFT+ENTER as it is an array formula besides check yr data cause in C1 you have "NutItion" instead of "NutRItion" On 12 Gru, 15:01, KCR wrote: Hello I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition *is in column C OR Nutrition is in column D A * * * * * * *B * * * * * * *C * * * * * * *D PwC * * * * *New * * * * Nutition * * Writing Carer * * * *Return * * *Writing * * *Nutrition PwC * * * * *New * * * * Writing * * *Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
Would you ever have Nutrition in BOTH columns of the same row? If not, then
the OR can just be a sum. Instead of --(C4:C87="Nutrition"), which generates 1's where there's a match and 0's elsewhere, use (--(C4:C87="Nutrition") + --(D4:D87="Nutrition")). (If Nutrition could be in both columns of the same row, this would double-count such rows, which you could back out separately). "KCR" wrote: Hello I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition is in column C OR Nutrition is in column D A B C D PwC New Nutition Writing Carer Return Writing Nutrition PwC New Writing Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
Using SUMPRODUCT, you can do it this way:
=SUMPRODUCT((B4:B87="New")*(A4:A87="PwC")*((C4:C87 ="Nutrition")+ (D4:D87="Nutrition"))) Note the similarity with Jarek's SUM(IF( formula (though you don't need to commit it with CSE). Hope this helps. Pete On Dec 12, 2:01*pm, KCR wrote: Hello I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition *is in column C OR Nutrition is in column D A * * * * * * *B * * * * * * *C * * * * * * *D PwC * * * * *New * * * * Nutition * * Writing Carer * * * *Return * * *Writing * * *Nutrition PwC * * * * *New * * * * Writing * * *Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
=SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--((C4:C87="Nutrition")+(D4:D87="Nutrition")0))
"Jarek Kujawa" wrote: don't know how to achieve that with SUMPRODUCT but you might try: =SUM(IF((B4:B87="New")*(A4:A87="PwC")*((C4:C87="Nu trition")+ (D4:D87="Nutrition")),1,)) insert it with CTRL+SHIFT+ENTER as it is an array formula besides check yr data cause in C1 you have "NutItion" instead of "NutRItion" On 12 Gru, 15:01, KCR wrote: Hello I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition is in column C OR Nutrition is in column D A B C D PwC New Nutition Writing Carer Return Writing Nutrition PwC New Writing Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using same fomula to count AND and OR options / sumproduct
=SUMPRODUCT(--(B4:B6="New"),--(A4:A6="PwC"),(C4:C6="Nutrition")+(D4:D6="Nutritio n"))
Note the answer will be 2 after correcting type in C1 Note also we no longer need to double negation since the addition operator now does the conversion of TRUE/FALSE to 1/0. Bit there would be no harm using it. For logic problems like this, I tell myself: Use * (Multiplication) for AND Use + (Addition) for OR Lets use numbers 0/1 for TRUE/FALSE and look at (C4:C6="Nutrition")+(D4:D6="Nutrition") (which is only part of out problem) {1, 0, 0} + ( 0, 1, 1} = {1,1,1} But (C4:C6="Nutrition")*(D4:D6="Nutrition") give {1, 0, 0} * ( 0, 1, 1} = {0,0,0} best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "KCR" wrote in message ... Hello I'm trying to figure out how to adapt a sumproduct formula so that i can count the number of rows where, for example, PwC is in column A New is in column B Nutrition is in column C OR Nutrition is in column D A B C D PwC New Nutition Writing Carer Return Writing Nutrition PwC New Writing Nutrition The answer should be 2 using the table above. I have been using this sumproduct fomula to count where three variables occur, but can't see how to adapt it. =SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--(C4:C87="Nutrition")) Help would be very much appreciated! KC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What are the COUNT or other options ... | Excel Worksheet Functions | |||
Count/sumproduct | Excel Discussion (Misc queries) | |||
count & If fomula | Excel Worksheet Functions | |||
Fills Series options sometimes messes the count!!! | Excel Discussion (Misc queries) | |||
Count if and Sumproduct | Excel Discussion (Misc queries) |