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

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

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

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

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

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