Counting Consecutive occurences a letter appears in a row

[Ashley137]

I use a excel sheet to keep track of employee holiday, sickness and rota'd shift. This is done by marking "D" as day to work "S" for sick "T" for training and "H" for holiday. With blanks in between for days off. I need a way of tracking the occurrences that someone is sick in a year.

I have attached a copy of what it looks like.

A3 is the row with the dates in.
So in Row 7 I want it to give a value of 2. As there is 2 occurrences of sick as there was a day worked in between.
Then in Row 9 it should give a value of 1, as it is 2 days but 1 occurrence.

If anyone can give me an answer it would be great have been searching a long time to try and work a solution!

[FDibbins]

HI, welcome to the forum

To do a count like this, you would probably need to use =COUNTIF()
=COUNTIF(row-range,"D")

[kvsrinivasamurthy]

Pl see attached file with UDF CountIf_Special

Please Login or Register  to view this content.

[Glenn Kennedy]

You posted a picture. On an Excel forum that's of limited use. An Excel sheet is FAR better. However, adjust the rnges in this to suit your sheet, and it should work perfectly. If not, attach an Excel sheet.

=SUM(IF(FREQUENCY(IF(D5:Y5="S",COLUMN(D5:Y5)),IF(D5:Y5<>"S",COLUMN(D5:Y5))),1))

[FDibbins]

yeah, I should have focused more on the heading, and less on the post - missed that 1 altogether

[Ashley137]

This is very close to what I want, so thank you for your response.
But the correct answer for "S" that I want should be 4. As I don't want it to stop counting the occurrence til a day has been worked again.
So K4 - O4 as in your spreadsheet would be one occurrence as a day hadn't been worked in between.

[Ashley137]

Pl see attached file with UDF CountIf_Special

Please Login or Register  to view this content.

This is very close to what I want, so thank you for your response.
But the correct answer for "S" that I want should be 4. As I don't want it to stop counting the occurrence til a day has been worked again.
So K4 - O4 as in your spreadsheet would be one occurrence as a day hadn't been worked in between.

[Glenn Kennedy]

Try this array formula out (using kvsrivasamurthy's excel sheet)

=SUM(IF(FREQUENCY(IF(A4:R4="S",IF(A4:R4<>"",COLUMN(A4:R4))),IF(A4:R4<>"S",IF(A4:R4<>"",COLUMN(A4:R4)))),1))


Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Glenn Kennedy]

Here's the sheet with the formula in place.

[Ashley137]

Works perfectly! Very much appreciated for your help!

[Glenn Kennedy]

You're welome and thanks for the rep...

[samba_ravi]

Try this array formula out (using kvsrivasamurthy's excel sheet)

=SUM(IF(FREQUENCY(IF(A4:R4="S",IF(A4:R4<>"",COLUMN(A4:R4))),IF(A4:R4<>"S",IF(A4:R4<>"",COLUMN(A4:R4)))),1))


Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

=SUM(IF(FREQUENCY(IF(A4:R4="S",IF(A4:R4<>"",COLUMN(A4:R4))),IF(A4:R4<>"S",IF(A4:R4<>"",COLUMN(A4:R4)))),1))
I think "IF(A4:R4<>""," is not required