|
|
| Count
Instances where X Occurs 10 Times in any Row |
The
following formulas count instances where X
occurs 10 times in any row within B2:K5...
=SUM(IF(FREQUENCY(IF(B2:K5="X",ROW(B2:K5)),ROW(B2:K5))=10,1))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
or
=SUM(IF(MMULT(--(B2:K5="X"),TRANSPOSE(COLUMN(B2:K5)^0))=10,1))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
or
=SUMPRODUCT(--(COUNTIF(OFFSET(B2:K5,ROW(B2:K5)-ROW(B2),0,1),"X")=10))
Based on the sample data, the formulas return 2. Sample
Workbook
|
|
|
