How to count array with OR condition

[Jimbo213]

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))*(D15="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?

 

[Jimbo213]

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

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

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

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))*(D15="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?

 

[Bernd P]

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

Regards,
Bernd

 

[T. Valko]

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

Try this:

=SUMPRODUCT(--(MMULT(--(A1:C5=3),{1;1;1})>0),--(D15="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),--(D15="R"))