Suming a row if if the week number matches

[Craig_Moore]

hi all

Im trying to automate counting how many staff are in over a week but haven't go a clue in how to do this

the totals for the days are in row 28 as shown below and im trying to count each column and get the result in to the totals in cells ao9:ao12

any help will be greatly recived

Craig

LABOUR LOG MARCH.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P																					AK	AL	AM	AN	AO	AP
5	DATE	01	02	03	04	05	06	07	08	09	10	11
6		9	9	9	9	9	10	10	10	10	10	10
7	LAST NAME
8	MOORE	1					HOL	HOL	1	1																									Week	9
9	Hussain	1	OT-C				1	1	1	1																									Week	10
10	Marinova	HOL					HOL	HOL	HOL	HOL																									Week	11
11	Abid	1			OT-B	OT-B	1	1	1	1		OT-C																							Week	12
12	Patel	1					1	1	1	1																									Week	13
13	Patel	1					1	1	1	1
14	Limbada	1	OT-C	OT-C			1	1	1	1	OT-C	OT-C
15	Mahmood	1					1	1	SICK	1
16	Shah	1					1	1	1	1
17	Qureshi	1					1	1	1	1
18	Khan	1					1	1	1	1
19	Soomro	1					1	1	1	1
20	Pervez	1			OT-B	OT-B	1	1	1	1
21	Matloob	1					1	1	1	1		OT-C
22	Shahzad	1					1	1	1	1
23	Hussain	1					1	1	1	1
24	Ehsan	1					1	1	1	1
25	Afzal	1		OT-C			1	1	1	1	OT-C	OT-C
26	Ali	1			OT-B	OT-B	1	1	1	1	OT-C	OT-C
27	Shah	1					1	1	HOL	HOL
28		19	0	0	0	0	18	18	17	18	0	0
MAR

Cell Formulas
Range	Formula
F5	F5	=A4
G5:P5	G5	=F5+1
F6:P6	F6	=ISOWEEKNUM(F5)
E8:E27	E8	=IFERROR(IF(C8="","",VLOOKUP(C8,Table1,3,0)),"STAFF")
F28:P28	F28	=SUM(F8:F27)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F8:AJ102	Cell Value	="OTHER"	text	NO
F8:AJ102	Cell Value	="OT-A"	text	NO
F8:AJ102	Cell Value	="OT-B"	text	NO
F8:AJ102	Cell Value	="OT-C"	text	NO
F8:AJ102	Cell Value	="OT-D"	text	NO
F8:AJ102	Cell Value	contains "SICK"	text	NO
F8:AJ102	Cell Value	contains "HOL"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F8:AJ27	List	='LOOK UP'!$F$2:$F$34

 

[kevin9999]

Is this what you mean?

Book1
	E	F	G	H	I	J	K	L	M	N	O	P																					AK	AL	AM	AN	AO
5	DATE	1	2	3	4	5	6	7	8	9	10	11
6		9	9	9	9	9	10	10	10	10	10	10
7	LAST NAME
8		1					HOL	HOL	1	1																									Week	9	19
9		1	OT-C				1	1	1	1																									Week	10	71
10		HOL					HOL	HOL	HOL	HOL																									Week	11	0
11		1			OT-B	OT-B	1	1	1	1		OT-C																							Week	12	0
12		1					1	1	1	1																									Week	13	0
13		1					1	1	1	1
14		1	OT-C	OT-C			1	1	1	1	OT-C	OT-C
15		1					1	1	SICK	1
16		1					1	1	1	1
17		1					1	1	1	1
18		1					1	1	1	1
19		1					1	1	1	1
20		1			OT-B	OT-B	1	1	1	1
21		1					1	1	1	1		OT-C
22		1					1	1	1	1
23		1					1	1	1	1
24		1					1	1	1	1
25		1		OT-C			1	1	1	1	OT-C	OT-C
26		1			OT-B	OT-B	1	1	1	1	OT-C	OT-C
27		1					1	1	HOL	HOL
28		19	0	0	0	0	18	18	17	18	0	0
Sheet1

Cell Formulas
Range	Formula
G5:P5	G5	=F5+1
AO8:AO12	AO8	=SUMIF($F$6:$P$6,AN8,$F$28:$P$28)
E8:E27	E8	=IFERROR(IF(C8="","",VLOOKUP(C8,Table1,3,0)),"STAFF")
F28:P28	F28	=SUM(F8:F27)

 

[Craig_Moore]

hi Kevin

yes that is exactly what i mean,

thank you for this and well done for understanding my poor explanation

Craig

 

[kevin9999]

Happy to help Craig, and thanks for the feedback