Counting occurrences not values.. Counting blocks of words in columns

Hi,

Hope you can help, I've been looking for a solution for a while.

So I've got a rota:

I have a string of dates in columns, row 1 & under those will be shifts and/or a code to indicate what that user is doing that day i.e. "Sick".

I need a formula that can be tacked onto the end of the rota in each row for 400+ rows (once per user so it cannot be too heavy) that displays the occurrences of these codes.

By occurrences I mean blocks of sickness, if the user was sick once in week 1 & then 3 days sick in week 2 that'll be 2 occurrences but 4 days sickness.

Example:

01/02/2016 Sick
02/02/2016 0700-1530
03/02/2016 0700-1530
04/02/2016 Sick
05/02/2016 Sick
06/02/2016 Sick
07/02/2016 0700-1530
08/02/2016 0700-1530
09/02/2016 0700-1530
10/02/2016 Sick

This user has 3 occurrences of being off sick, however they've had 5 sick days.

Thanks

Ah, the old Bradford Factor. Suggest you post a sample workbook with some typical examples.

Try this:

Formula:

Please Login or Register  to view this content.

Confirmed with ctrl+shift+enter (not just enter)

Thanks for the replies so far. Not quite there yet. Attempting to upload an example document with your formula suggestions attempted so far.

Ah perfect. Superstar! I should've spotted that. Thank you i'll continue testing & work on the rest. Removing gaps where it's a weekend is the next challenge sick Friday & then Sick Monday should count as one occurrence.

In Tony's formula change ROW to COLUMN as your data is across COLUMNS not down ROWS

=SUM(IF(FREQUENCY(IF(B2:Q2="(SICK FULL)",COLUMN(B2:Q2)),IF(B2:Q2<>"(SICK FULL)",COLUMN(B2:Q2)))>0,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi,

I've sorted the weekend issue - however is there a way to include multiple code values within the formula? For example variations of "(SICK FULL)" where someone has entered the text incorrectly or in a different way. I.e. "SICK" / "(SICK HALF)" etc. ?

Or a way I can implement a wildcard on the word Sick?

Try

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("*SICK*",B2:Q2)),COLUMN(B2:Q2)),IF(NOT(ISNUMBER(SEARCH("*SICK*",B2:Q2))),COLUMN(B2:Q2)))>0,1))

Enter with Ctrl+Shift+Enter

Try

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("*SICK*",B2:Q2)),COLUMN(B2:Q2)),IF(NOT(ISNUMBER(SEARCH("*SICK*",B2:Q2))),COLUMN(B2:Q2)))>0,1))

Enter with Ctrl+Shift+Enter