Counting occurrences of sickness in a roster across a year

Seeing as you seem only to have access to 2003, would it be at all practical to have a helper row for each person?

You may use SUBSTITUTE() to remove OFF/ away from your exported data.
Could you please upload a sample or mock up data? (and output that you want)

Regards.

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does indicates older versions but you can add the recent ones.
Do you sill use 97/2003 ? Some functions in 2013 didn't exist at the time
Thanks

Originally Posted by GregM56

Or another way I could do it . . . see attachment

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

As a develop of formula in E6

1st step : concatenate all list

=F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6

at this point if you can use TEXTJOIN ( that avilable on Excel2016 and by some add-ins ) your code will be
=TextJoin( "" , True , F6:AB6 )

2nd step : remove 'OFF' away

=SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF","")

3rd step : reduce connect of SICKSICK.... become one as a new of token ; in this sample I use # ( choose any char that not presented in list )

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"SICK","#"),"##","#"),"##","#")

You can see I've subtitute it's for 3 times as different [ SICK -> # , ## -> # , ## -> # ] to reduce all SICK to single of #

4th step : count that token (final step)

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"SICK","#"),"##","#"),"##","#"))-
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"SICK","#"),"##","#"),"##","#"),"#",""))

Regards.

Perhaps this array formula:

=COUNT(1/FREQUENCY(IF(F21:AB21="SICK",COLUMN(F21:AB21)),IF(F21:AB21="DUTY",COLUMN(F21:AB21))))

After re-check it's seem that my solition not working for a long period sick. >_<"

Sorry.

Except in crease of subsitute as

E6

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"SICK","#"),"#####","#"),"####","#"),"###","#"),"##","#"))-
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"SICK","#"),"#####","#"),"####","#"),"###","#"),"##","#"),"#",""))

Regards.

The other solution is use UDF, I'm not sure that you can use VBA or not. >_<"

Function SickCount(ChkArea As Range)

    CPrv = ""
    For Each C In ChkArea
        If CPrv <> "SICK" Then
           If C = "SICK" Then
              CCount = CCount + 1
              CPrv = "SICK"
           End If
        End If
        If C = "DUTY" Then CPrv = "DUTY"
    Next
    
    SickCount = CCount

End Function

Usage is =SickCount( Range that you want to check )
E6

=SickCount(F6:AB6)

Regards.

After saw Greg's solution, suddenly I think this out, thanks to Greg. ^_^
E6

=(LEN("DUTY"&SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""))-LEN(SUBSTITUTE("DUTY"&SUBSTITUTE(F6&G6&H6&I6&J6&K6&L6&M6&N6&O6&P6&Q6&R6&S6&T6&U6&V6&W6&X6&Y6&Z6&AA6&AB6,"OFF",""),"DUTYSICK","")))/LEN("DUTYSICK")

if you can use textjoin

=(LEN("DUTY"&SUBSTITUTE(TextJoin("",TRUE,F6:AB6),"OFF",""))-LEN(SUBSTITUTE("DUTY"&SUBSTITUTE(TextJoin("",TRUE,F6:AB6),"OFF",""),"DUTYSICK","")))/LEN("DUTYSICK")

Regards.