Counting separate instances

[Marmaduke0703]

Hi,

I have a sheet that I need some help with please...

Each month, we need to summarise the absence information from a table similar to the below. I have a countif to total up the complete number of days but what we would like it know is how many separate instances there are for each row. Using the example below, Emp 1 and Emp 6 will need to show as 2 (currently hardcoded) as they came back into work ("W") between their two absences.

I've tried writing an if statement to compare the cell to the left (if the current day is "S" but the previous day is "W"...) but I need to account for any "R" or "H" values etc.

Any help will be greatly appreciated!
Thanks

Book1 (003).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Name	Count "S" Days	Count instances separated by work	01/10	02/10	03/10	04/10	05/10	06/10	07/10	08/10	09/10	10/10	11/10	12/10	13/10	14/10	15/10	16/10	17/10	18/10
4	Emp 1	4	2	R	R	S	S	W	W	R	R	S	S	W	W	R	R	W	W	W	W
5	Emp 2	1	1	R	R	H	H	H	H	R	R	W	R	R	S	W	W	W	H	H	R
6	Emp 3	0	0	H	H	R	R	W	W	W	W	R	R	W	W	W	W	R	R	W	W
7	Emp 4	0	0	R	W	W	W	R	R	W	W	W	W	W	R	R	W	W	W	W	R
8	Emp 5	8	1	W	W	W	H	R	R	S	S	S	S	R	R	S	S	S	S	R	R
9	Emp 6	9	2	S	S	S	S	R	R	W	W	W	R	R	S	S	S	S	S	R	R
10	Emp 7	13	1	S	S	S	S	R	R	S	S	S	S	S	R	R	S	S	S	S	R
11	Emp 8	2	1	S	R	R	S	W	W	W	R	R	W	W	W	W	R	R	W	W	W
12	Emp 9	0	0	R	W	W	W	W	W	R	R	W	W	W	W	R	R	H	H	H	H
13	Emp 10	14	1	S	S	S	S	S	R	R	S	S	S	S	R	R	S	S	S	S	S
14	Emp 11	0	0	H	H	H	H	R	R	W	W	W	W	W	R	R	W	W	W	W	R
15	Emp 12	0	0	W	W	W	W	R	R	W	W	W	W	W	R	R	W	W	W	W	W
16	Emp 13	2	1	R	W	W	W	W	W	R	R	W	W	W	W	R	R	S	S	W	W
17	Emp 14	6	1	W	W	R	R	S	S	S	S	R	R	S	S	W	W	R	R	W	W
18	Emp 15	2	1	S	S	W	W	R	R	W	W	W	W	W	R	R	H	H	H	R	R
19	Emp 16	3	1	W	W	R	R	S	S	S	W	W	R	R	W	W	W	R	R	W	W
20	Emp 17	12	1	R	S	S	S	S	R	R	S	S	S	S	R	R	S	S	S	S	R
21	Emp 18	12	1	S	R	R	S	S	S	S	R	R	S	S	S	S	R	R	S	S	S
Sheet1

Cell Formulas
Range	Formula
B4:B21	B4	=COUNTIF(D4:U4,"S")

 

[bobaol]

Hello, The CountIf should work okay. You can insert two more columns so that there is a column for H, R, W, S. Or, you could write the formula in columns AA, AB, AC, AD for HRWS. You don't need to show people your work, but you know where the work is.

 

[Marmaduke0703]

Hello, The CountIf should work okay. You can insert two more columns so that there is a column for H, R, W, S. Or, you could write the formula in columns AA, AB, AC, AD for HRWS. You don't need to show people your work, but you know where the work is.

Hi

Apologies, I don't think I explained it very well.

What I need to show is how many separate periods of absence someone had (not the total days). So say they were "S" on monday and tuesday (first period of absence), came back into work ("W") on wednesday and thursday, then were absent ("S") again on friday (second absence) it would show as 2 separate instances of absence as they are separated by an attendance.

The other thing I need to consider is rest days ("R") as these should not be considered as a break in absence ("S" on friday, "R" on Saturday & Sunday, "S" on monday will count as one period of absence).

I hope that helps clarify what my problem is

Thanks,

 

[bobaol]

woof, try the file in DropBox. Essentially, I broke it down to each section so it is as simple as possible. That is, one single formula in a cell, or as few formulas in a cell as possible. Instead of a ton of IF statements in one cell. Then, build upon each section, then consolidate the answer in the summary section. the R and S was not too hard. But the R-only and not counting the R made it more difficult. the formulas go to row 2200. Each different section is based on a relative reference, starting in row 11. Hope it helps. HRSW_xlsx_.xlsx

 

[Marmaduke0703]

woof, try the file in DropBox. Essentially, I broke it down to each section so it is as simple as possible. That is, one single formula in a cell, or as few formulas in a cell as possible. Instead of a ton of IF statements in one cell. Then, build upon each section, then consolidate the answer in the summary section. the R and S was not too hard. But the R-only and not counting the R made it more difficult. the formulas go to row 2200. Each different section is based on a relative reference, starting in row 11. Hope it helps. HRSW_xlsx_.xlsx

Thank you so much!!

I never would have got that Really appreciate your help!

 

[Marmaduke0703]

So I've been playing around with your idea to fit it in to my spreadsheet and followed the same logic to come up with this which does all I need it to Thanks again for your help!

Book1 (1).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Sick
2
3	Name		Total "S" days	Separate "S" instances		20/12	21/12	22/12	23/12	24/12	25/12	26/12	27/12	28/12	29/12	30/12	31/12	01/01	02/01
4	Emp 1		7	2		R	R	S	S	S	H	R	R	R	R	S	S	S	S
5	Emp 2		6	1		S	S	R	R	R	R	S	S	S	S	R	R	R	R
6	Emp 3		4	3		S	H	R	R	R	R	S	S	w	S	R	R	R	R
7	Emp 4		3	2		S	W	R	R	R	R	S	S	W	W	R	R	R	OT
8	Emp 5		6	1		R	S	S	S	S	S	R	R	S	W	W	W	W	R
9	Emp 6		3	1		H	H	R	R	R	R	W	S	S	S	R	R	R	R
10
11
12
13	Overwrite rest break to create continuous "S" period
14			S
15	Emp 1							S	S	S						S	S	S	S
16	Emp 2					S	S	S	S	S	S	S	S	S	S	S	S	S	S
17	Emp 3					S						S	S		S	S	S	S	S
18	Emp 4					S						S	S
19	Emp 5						S	S	S	S	S	S	S	S
20	Emp 6												S	S	S	S	S	S	S
21
22
23	Take first date
24	Emp 1		2					1								1
25	Emp 2		1			1
26	Emp 3		3			1						1			1
27	Emp 4		2			1						1
28	Emp 5		1				1
29	Emp 6		1										1
30
31
Sheet1

Cell Formulas
Range	Formula
D4:D9	D4	=COUNTIF(G4:T4,$D$14)
E4:E9	E4	=D24
H15:T21	H15	=IF(H4=$D$14,H4,IF(AND(G15=$D$14,H4="R"),$D$14,""))
G15:G20	G15	=IF(G4=$D$14,G4,"")
G24:G29	G24	=IF(G15="","",1)
H24:T29	H24	=IF(AND(H15<>"",G15<>""),"",IF(H15<>"",1,""))
D24:D29	D24	=SUM(G24:T24)