Count the Number of Times "X" Occurs 10 Times in any Row

Counting the Number of Times "X" Occurs 10 Times in any Row

The following formulas count the number of times "X" occurs 10 times in any row within the range A2:J5...

=SUM(IF(FREQUENCY(IF(A2:J5="X",ROW(A2:J5)),ROW(A2:J5))=10,1))

or

=SUM(IF(MMULT(--(A2:J5="X"),TRANSPOSE(COLUMN(A2:J5)^0))=10,1))

or

=SUMPRODUCT(--(COUNTIF(OFFSET(A2:J5,ROW(A2:J5)-ROW(A2),0,1),"X")=10))

Note that the first two formulas need to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

Based on the sample data, the formulas return 2.

Sample Workbook: Download