Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 2, 2024
Count the Number of Times 'X' Occurs 10 Times in any Row
The following formula counts the number of times 'X' occurs 10 times in any row within the range B3:K12...
=SUM(BYROW(B3:K12,LAMBDA(row,(COUNTIF(row,"X")=10)+0)))
For earlier versions of Excel...
=SUM(IF(FREQUENCY(IF(B3:K12="X",ROW(B3:K12)),ROW(B3:K12))=10,1))
or
=SUM(IF(MMULT(--(B3:K12="X"),TRANSPOSE(COLUMN(B3:K12)^0))=10,1))
or
=SUMPRODUCT(--(COUNTIF(OFFSET(B3:K12,ROW(B3:K12)-ROW(B3),0,1),"X")=10))
Note that the first two formulas need to be confirmed with CTRL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 5.
Sample Workbook: Download