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

1. ## 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))}

2. ## Re: Count the number of times an A appears 5 or more times consecutively

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.

3. ## Re: Count the number of times an A appears 5 or more times consecutively

Biff,
Thank you for the reply. The problem I am now having is that it does not count any additional "A"'s it only counts the one instance of 5 or more...when I enter 2 more groups of 5 absences it still only counts 1?

10-1-2013 9-11-31 AM.png

4. ## Re: Count the number of times an A appears 5 or more times consecutively

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.

5. ## Re: Count the number of times an A appears 5 or more times consecutively

it would be 17 consecutive, but if I put them in groups of 5 then I would acutally hve 15 days. I am not concerned about the other 2 days on the thursday and friday , I want to count them in groups of 5. the idea is that when someone is absent for 5 consecutive days a notice needs to be sent, then when they have 10 (an occurance of 5 more consecutive at any point) another notice is sent, and so on...the ignoring X's is to count a friday and then a monday-thursday absence that should be 5 consecutive school days out.

6. ## Re: Count the number of times an A appears 5 or more times consecutively

Sorry, I don't understand.

7. ## Re: Count the number of times an A appears 5 or more times consecutively

In the example attached the student has 18 days absent, but I only care about an occurance of 5 consecutive days so while I am counting overall days, I am not concerned with the 2 absences on the 2nd and 3rd, but I am looking at the ones where a student i out on a friday and then m-th or wed-friday and then again on mon-tues. I hope this helps
10-2-2013 9-33-04 AM.png

8. ## Re: Count the number of times an A appears 5 or more times consecutively

Sorry, still not registering.

Maybe a fresh set of eyes will see it.

9. ## Re: Count the number of times an A appears 5 or more times consecutively

No worries. thank you for your help

10. ## Re: Count the number of times an A appears 5 or more times consecutively

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!

11. ## Re: Count the number of times an A appears 5 or more times consecutively

Can't you just use the formula you have in post one and use a substitute function to take out the X's where neccessary?

e.g.
{=SUM(IF(FREQUENCY(IF(SUBSTITUTE(\$F6:\$LE6,"X","")="A", and so on

Edit: just looked at it again. No you can't...

12. ## Re: Count the number of times an A appears 5 or more times consecutively

Had another go and essentially based on Tony's solution:
=SUM(IF(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))>=5,ROUNDDOWN(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5,0)))

13. ## Re: Count the number of times an A appears 5 or more times consecutively

This worked perfect!! Thank you so much

14. ## Re: Count the number of times an A appears 5 or more times consecutively

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 -

Incase your issue is not solved, you can undo it as follows -

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.

15. ## Re: Count the number of times an A appears 5 or more times consecutively

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

16. ## Re: Count the number of times an A appears 5 or more times consecutively

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.

17. ## Re: Count the number of times an A appears 5 or more times consecutively

I wanted to be able to count the number of time a student is absent 5 consecutive days, The A indicated an absence and the X's were non school days. I placed conditional formatting to tell me when a student had an occurance of 5 consecutive days, then if they had another consecutive 5 day regardless of when in the year it would cause the flag to change to red because each occurance required a different notice. Please let me know if this helped explain what my original need was, I would be more than happy to elaborate more.

18. ## Re: Count the number of times an A appears 5 or more times consecutively

OK, if we consider this sample:

AAXXAAAAAXXAAAAAXXAAAAA

Then the result you expect is 3?

19. ## Re: Count the number of times an A appears 5 or more times consecutively

@Tony,

Aye, that's my understanding. Thinking about it I could probably just do:
=SUM(ROUNDDOWN(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5,0))

-I used your formula which will give the numbers of consecutive A's as an array, dividing these by 5 and rounding down would then give the number of times A appears 5 times in a row in each group of consecutive A's. Then the sum of these is the OP's target.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1