=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