Count cells in row (range) with a value that are not consecutive

[jjones312]

I have a spreadsheet that calculates employee absences and tardiness. There is a policy rule that if the absence is back-to-back, it only counts as a single occurrence. Currently I'm using "=COUNTIF(January[@[1]:[31]],"US")" to count all cells in a row that contains "US". Now I need to apply the policy piece.

Here is an example:
Row 1: Has entries for "US" twice on non-consecutive days. This should total in the column "US", column AK as 2 occurrences.
Row 2: Has three entries for "US" back to back, consecutive days. This should only count as 1 occurrence.
Row 3: Has four entries for "US" back-to-back and non-consecutive days. This should add up to three occurrences.

Can anyone assist in the formula for this? I'm at a lost at the moment.

 

[jjones312]

It is deliberately offset, that way in checks if B4=US & if A4 <>US to see if the cell to the left contains US or not.

FYI..

It works perfectly on teams with the formula, and the supervisors are so STOKED!!!! Thank you

 

[Fluff]

My pleasure.