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



## jjones312 (Tuesday at 6:43 PM)

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.


----------



## DanteAmor (Tuesday at 8:05 PM)

Try with this UDF:


```
Function count_absence(rng As Range, txt As String)
  Dim i As Long, n As Long
  Dim a As Variant
  Dim b As Boolean
  a = rng.Value
  b = False
  For i = 1 To UBound(a, 2)
    If UCase(a(1, i)) = UCase(txt) Then
      If b = False Then
        n = n + 1
        b = True
      End If
    ElseIf a(1, i) = "" Then
      b = False
    End If
  Next
  count_absence = n
End Function
```

*HOW TO INSTALL UDFs*
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use *count_absence *just like it was a built-in Excel function. For example:

Dante AmorABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF12sunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontue312345678910111213141516171819202122232425262728293031Total Days4USUS25USUSUS16USUSUSUSUSUS47USUSUS18USUSUSUS39USUSUS310USUS111USUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUS112USUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUS113USUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUSUS214USUS2SH7Cell FormulasRangeFormulaAF4:AF14AF4=count_absence(January[@[1]:[31]], "US")


----------



## Fluff (Wednesday at 7:38 AM)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## Fluff (Wednesday at 10:12 AM)

If you are using 365 or 2021 how about
Fluff.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAHAIAJAK12sunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontue312345678910111213141516171819202122232425262728293031USL74USUSL7L7USL7325USUSUSL7L7L7L7126USUSUSL7L7US327L7USUSUSUS218USUSUSUS30TempCell FormulasRangeFormulaAJ4:AK8AJ4=SUM(SIGN(IF(($B4:$AF4=AJ$3)*($A4:$AE4<>AJ$3),1,0)))


----------



## DanteAmor (Wednesday at 12:52 PM)

Fluff said:


> If you are using 365 or 2021 how about


Hi Dave, and Happy New Year   

It also works in excel 2023 as array formula (CSE).


----------



## Fluff (Wednesday at 12:59 PM)

Happy New Year to you as well.
Thanks for letting us know it works in older versions as well.


----------



## jjones312 (Yesterday at 11:18 AM)

DanteAmor said:


> Try with this UDF:
> 
> 
> ```
> ...


This is why I come to this forum; even if there are long periods in between, everyone here is so knowledgeable and quick with the solutions. This works flawlessly, kind of. I appreciate the work, and like I said, it works perfectly, but the supervisors who use the sheet collaborate using Teams.  So UDF doesn't work when posted on Teams, and they use it out of the browser.  Unless there is away?  I even tried to save it as an "add-in" for use, but still, when opening it in Teams, it created the #name error.

Again great work and this solution is valid.


----------



## jjones312 (Yesterday at 11:19 AM)

Fluff said:


> What version of Excel are you using?
> 
> I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


updated


----------



## jjones312 (Yesterday at 11:34 AM)

Fluff said:


> If you are using 365 or 2021 how about
> Fluff.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAHAIAJAK12sunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontuewedthufrisatsunmontue312345678910111213141516171819202122232425262728293031USL74USUSL7L7USL7325USUSUSL7L7L7L7126USUSUSL7L7US327L7USUSUSUS218USUSUSUS30TempCell FormulasRangeFormulaAJ4:AK8AJ4=SUM(SIGN(IF(($B4:$AF4=AJ$3)*($A4:$AE4<>AJ$3),1,0)))


So this one works as well desktop.. About to test in Teams and will let you know.

I do have one question, though, about the formula that I'm not quite understanding.   In the formula, in the logical test of the IF function, you have ($A4:$AE4<>AJ$3)   Why this range?  Seems to offset the left by one cell, which seemed off to me but worked.   Help me understand?


=SUM(SIGN(IF(($B4:$AF4=AJ$3)*($A4:$AE4<>AJ$3),1,0)))


----------



## Fluff (Yesterday at 11:59 AM)

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.


----------



## jjones312 (Tuesday at 6:43 PM)

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 (Yesterday at 1:24 PM)

Fluff said:


> 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 (Yesterday at 1:30 PM)

My pleasure.


----------

