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, 14 views)

If I enter your formula to H1:H4 I get the results 0,1, 0, 0. You seem to indicate that you get correct result without COUNTIF.

Is that what you say?

Is it important for you to avoid a helper column?

Hi Xor. If a helper column (Col H) could be avoided through a straight formula in B6 to return "2", I would prefer that. In the sample sheet, there are 2 rows that are populated with at least five consecutive "x".

I have no idea how to solve it without a helper column, but if you already have that solution yourself (as seem to be indicated in your first post) I wouldn't bother you anymore.

It's no bother because I'm learning so much from this forum. I guess I must add a helper column, apply the formula in the first post then make a summation of the results. Thanks..

Yes, but I only don't understand how you can use the first formula. As I wrote in #2, for me it returns 0, 1, 0, 0 when entered in H1 and copied down to H4, but as you wrote yourself there are two rows that fulfil the criteria (contain 5 consecutive X.)

[QUOTE]As I wrote in #2, for me it returns 0, 1, 0, 0 when entered in H1 and copied down to H4, but as you wrote yourself there are two rows that fulfil the criteria (contain 5 consecutive X.)[QUOTE]


Oh.. Maybe I accidentally deleted another X in row 3 because the formula should return 0,1,1,0. I'm re attaching the file.

Attached Files

X file.xlsx (14.9 KB, 10 views)

In the formula in your first post you used:

=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)) and in the formula in the most recent file:

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

Another possibility is the somewhat shorter formula:

=--(MAX(FREQUENCY(IF(ISNUMBER(SEARCH("X",$A1:$G1)),CO LUMN($A1:$G1)),IF(NOT(ISNUMBER(SEARCH("X",$A1:$G1) )),COLUMN($A1:$G1))))>=5)

Thanks Xor, I adopted the shorter formula. Until now I could not make may brain cells to understand the two minuses before the formula. I came across the same functions when counting values in a range except blanks containing formulae. Also, I erroneously thought IFERROR and IF(ERROR) work the same.

…or write your own user-defined function. See B6 in the attached.
=CountOfRowsWithNOrMoreConsecutiveXs(A1:G4,5)
(Case insensitive)

Attached Files

msofficeforums40949file.xlsm (15.5 KB, 7 views)

Magic Cal, thank you many times.