Using same fomula to count AND and OR options / sumproduct

  • Thread starter Thread starter KCR
  • Start date Start date
K

KCR

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
 
don't know how to achieve that with SUMPRODUCT

but you might try:

=SUM(IF((B4:B87="New")*(A4:A87="PwC")*((C4:C87="Nutrition")+
(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"
 
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).
 
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
 
=SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--((C4:C87="Nutrition")+(D4:D87="Nutrition")>0))
 
=SUMPRODUCT(--(B4:B6="New"),--(A4:A6="PwC"),(C4:C6="Nutrition")+(D4:D6="Nutrition"))
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
 
Back
Top