Hi. I have a sheet that contains characters combined with "X". I would like to count the number of rows that contain 5 consecutive cells with the letter "X". I found the below array formula that i think would be a jump off point for me to get the number of rows but couldn't seem to come to the correct result when I added COUNTIF before the SUM. If possible, I wouldn't need to add a helper column.


=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("X",$A1:$G1)) ,COLUMN($A1:$G1)-COLUMN($A1)+1),IFERROR(SEARCH("X",$A1:$G1),COLUMN( $A1:$G1)-COLUMN($A1)+1))>=5,1))


Thank you.


Um, how do I attach a screenshot in a post?

Attached Files

X file.xlsx (14.3 KB, 16 views)