Using same fomula to count AND and OR options / sumproduct

[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

 

[Jarek Kujawa]

don't know how to achieve that with SUMPRODUCT

but you might try:

=SUM(IF((B4:B87="New")*(A4:A87="PwC")*((C4:C87="Nutrition")+
(D487="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"

 

[bpeltzer]

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") +
--(D487="Nutrition")).
(If Nutrition could be in both columns of the same row, this would
double-count such rows, which you could back out separately).

 

[Pete_UK]

Using SUMPRODUCT, you can do it this way:

=SUMPRODUCT((B4:B87="New")*(A4:A87="PwC")*((C4:C87="Nutrition")+
(D487="Nutrition")))

Note the similarity with Jarek's SUM(IF( formula (though you don't
need to commit it with CSE).

Hope this helps.

Pete

 

[Teethless mama]

=SUMPRODUCT(--(B4:B87="New"),--(A4:A87="PwC"),--((C4:C87="Nutrition")+(D487="Nutrition")>0))

 

[Bernard Liengme]

=SUMPRODUCT(--(B4:B6="New"),--(A4:A6="PwC"),(C4:C6="Nutrition")+(D46="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")+(D46="Nutrition") (which is only part of out problem)
{1, 0, 0} + ( 0, 1, 1} = {1,1,1}
But
(C4:C6="Nutrition")*(D46="Nutrition") give
{1, 0, 0} * ( 0, 1, 1} = {0,0,0}
best wishes