Count the number of times an A appears 5 or more times consecutively

I am trying to count the number of times an A appears in a column 5 or more times in a row, the question I have is there are cells which have an "X" in them and I want it to ignore the X value when considering if an A appears consecutively. This is an attendance sheet so the "X" values are for non work days i.e. Sat/Sun. So if someone is out on Friday and then out Mon-Thursday it should count the "A"'s as 5 consecutive values.

16 17 18 19 20 21 22 23 24 25 26
P P P P A X X A A A A

result for count of cnsecutive "A" should be 5.

Here is the formula I have, but it does not exclude the X's

{=SUM(IF(FREQUENCY(IF($F6:$LE6="A",COLUMN($F6:$LE6)),IF($F6:$LE6<>"A",COLUMN($F6:$LE6)))>=5,FREQUENCY(IF($F6:$LE6="A",COLUMN($F6:$LE6)),IF($F6:$LE6<>"A",COLUMN($F6:$LE6))),0))}

I didn't look at your file.

Try something like this:

=SUM(IF(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))>=5,1))

Still array entered.

I'm not sure I understand what you want to count.

You said:

I want it to ignore the X value when considering if an A appears consecutively.

So, in your screencap if you ignore the X's then there is just a single streak of 5 or more consecutive A's.

AAXXAAAAAXXAAAAAXXAAAAA

Ignoring the X's, that would be counted as 17 consecutive A's.

Sorry, I don't understand.

Sorry, still not registering.

Maybe a fresh set of eyes will see it.

I posted a link to this thread in the "Call in the Cavalry" subforum.

Maybe someone will see what I'm not seeing.

Good luck!

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

You could avoid repeating the FREQUENCY function by using this version

=SUM(INT(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5))

confirmed with CTRL+SHIFT+ENTER

Looks like you solved it but can you explain to me the logic starting from the ROUNDDOWN function?

I understand the "mechanics" of the formula but I don't understand what the OP wanted to do. If I understand the formula logic then I'll be able to understand what the OP wanted.

OK, if we consider this sample:

AAXXAAAAAXXAAAAAXXAAAAA

Then the result you expect is 3?