How to count array with OR condition
 

Hi

I have searched this group and not found an answer.

I need
1) a count of the number of rows having a 3 in ANY of the three columns
2) where column D contains R

A B C D
1 1 3 3 R
2 1 1 2 x
3 2 1 1 x
4 1 3 4 x
5 3 1 3 R

The answer should be 3 [row 1,5] because those rows have a 3 in A or B or C
AND column D=R

I've tried an array formula

=SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because
the * operator is treated as an AND

Is there a different operator that is treated as an OR condition?

--
Thanks for your reply & assistance.
Jimbo213

Fixed typo: How to count array with OR condition
 

corrected typo: see corrected post below
- added R in cell D3
- formula result should be 2, not 3

"Jimbo213" wrote:


Hi

I have searched this group and not found an answer.

I need
1) a count of the number of rows having a 3 in ANY of the three columns
2) where column D contains R

A B C D
1 1 3 3 R
2 1 1 2 x
3 2 1 1 R
4 1 3 4 x
5 3 1 3 R

The answer should be 2 [row 1,5] because those rows have a 3 in A or B or C
AND column D=R

I've tried an array formula

=SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because
the * operator is treated as an AND

Is there a different operator that is treated as an OR condition?

--
Thanks for your reply & assistance.
Jimbo213

Fixed typo: How to count array with OR condition
 

=SUM(IF((D1:D5="R")*((A1:A5=3)+(B1:B5=3)+(C1:C5=3) ),1))

ctrl+shift+enter, not just enter


"Jimbo213" wrote:


corrected typo: see corrected post below
- added R in cell D3
- formula result should be 2, not 3

"Jimbo213" wrote:


Hi

I have searched this group and not found an answer.

I need
1) a count of the number of rows having a 3 in ANY of the three columns
2) where column D contains R

A B C D
1 1 3 3 R
2 1 1 2 x
3 2 1 1 R
4 1 3 4 x
5 3 1 3 R

The answer should be 2 [row 1,5] because those rows have a 3 in A or B or C
AND column D=R

I've tried an array formula

=SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because
the * operator is treated as an AND

Is there a different operator that is treated as an OR condition?

--
Thanks for your reply & assistance.
Jimbo213

How to count array with OR condition
 

=sumproduct(sign(0+(a1:a5=3)+(b1:b5=3)+(c1:c5=3)),--(d1:d5="R"))

Regards,
Bernd

How to count array with OR condition
 

As long as you have less than ~5400 rows of data...

Try this:

=SUMPRODUCT(--(MMULT(--(A1:C5=3),{1;1;1})0),--(D1:D5="R"))

Or, with this one, no row limit if you're using Excel 2007, 65535 row limit
in other versions of Excel:

=SUMPRODUCT(--((A1:A5=3)+(B1:B5=3)+(C1:C5=3)0),--(D1:D5="R"))

--
Biff
Microsoft Excel MVP


"Jimbo213" wrote in message
...

Hi

I have searched this group and not found an answer.

I need
1) a count of the number of rows having a 3 in ANY of the three columns
2) where column D contains R

A B C D
1 1 3 3 R
2 1 1 2 x
3 2 1 1 x
4 1 3 4 x
5 3 1 3 R

The answer should be 3 [row 1,5] because those rows have a 3 in A or B or
C
AND column D=R

I've tried an array formula

=SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because
the * operator is treated as an AND

Is there a different operator that is treated as an OR condition?

--
Thanks for your reply & assistance.
Jimbo213