COUNT occurence formula help

[Jacko1307]

Good morning,

I have been asked to produce a variance of staff sick days within my workbook.

1. Currently I have the following formula =COUNTIF(B6:NB6,"S") However my boss has asked me to also add a variation to this in Occurence format.

Question. If an individual was off for a 3 day period then back in work for 5 weeks then off for a 5 day period how could I show this as 2 occurences not total of days ?

Any help on this would be much appreciated.

Many thanks

Martin

 

[JvdV]

I made some sample data myself how I would imagine your data to be looking like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	w	s	s	s	w	w	w	w	w	s	s	s	s	s
2
3	2
Sheet6

Cell Formulas
Range	Formula
A3	A3	=SUM(--(FREQUENCY(IF(A1:N1="s",COLUMN(A1:N1)),IF(A1:N1="s",0,COLUMN(A1:N1)))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.