Sum by Name, Project and Date (in column)

[PaulyK]

I have a spreadsheet containing Projects and Employee Resources. Each Month the allocated number of days per project to be worked are entered into the month column. I am looking for a way to be able to sum the amount of days worked, per project, per employee each month. I have used Sumifs to get a Total for the amount of days, per project for a specific month, but not with all the criteria (By Month, Employee, Project) to summarize in a separate worksheet. I can Sum the totals for a single Month column (Jan-22 in this case) by Project and Employee using the below formula. But how can I make it just use a specific month date? I have added an example below. It may be the case that there is a much easier approach - i only learnt SUMIFS recently!

Project	Employee	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
Project 1	Person 1	2	0	1	12	12	13	0	4	4	1
Project 1	Person 2	6	4	0	0	0	6	0	3	11	1.5
Project 1	Person 3	20	3	12	3	3	2	1	10	1	2
Project 1	Person 6	2	5	3	4	4	4	2	10	1	2.5
Project 2	Person 1	2	1	1	3	3	0	0	4	0	3
Project 2	Person 3	2			4	0	0	0	3	8	3.5
Project 3	Person 3	0	8	11	19	0	0	0	4	3	4
Project 3	Person 3	4	6	4	6	4	6	4	6	4	4.5
Project 4	Person 2	12	2	8	10.5	2	4.5	3	5.5	5	5
Project 5	Person 1	1	0	0	8.5	0	0	10	0	6	5.5
Project 6	Person 7	1	0	0	2.5	0		10	7	7	6
Project 7	Person 1	1	0	0	3	0.5	0	4	0	0	6.5
Project 7	Person 5	20	20	21	18	3	3	3	3	0	7
Project 7	Person 6	3	0.5	12	1	1	6	4	10	10	21
Project 8	Person 1	2	0.5	0	0	0	0	0	0	8	22
Project 9	Person 3	3	3	3	3.5	0	0	0	0	3	0
Project 9	Person 6	0		7.5	10.5	18.5	1	2	5	6	0
Project 9	Person 7	0	1	2	5	7	8	0	1.5	7	0
Project 9	Person 8	5	5	5	10	0.5	0.5	1	0.5	9	0
Project 10	Person 1	6	6	6	6	6	6	6	1	1	0
Project 11	Person 4	2	3	4	5	5	12	5	0	2	1
Project 12	Person 4	3	1	4	6	9	1	4	9	3	2
Project 13	Person 2	0	1	5	4	3	5	4	3	4	1
Project 14	Person 5	1	1	1	1	1	0	0	0	1	2
Project 15	Person 8	1	2	3	4	5	6	7	0	8	0

SUMMARY sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Project	Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10		Month		Jan-22
2		Project 1	2											Jan-22		Feb-22
3		Project 2	2													Mar-22
4		Project 3	0													Apr-22
5		Project 4	0													May-22
6		Project 5	1													Jun-22
7		Project 6	0													Jul-22
8		Project 7	1													Aug-22
9		Project 8	2													Sep-22
10		Project 9	0													Oct-22
11		Project 10	6													Nov-22
12		Project 11	0													Dec-22
13		Project 12	0													Jan-23
14		Project 13	0													Feb-23
15		Project 14	0													Mar-23
16		Project 15	0													Apr-23
17																May-23
18																Jun-23
19																Jul-23
20																Aug-23
21																Sep-23
22																Oct-23
23																Nov-23
24																Dec-23
Summary

Cell Formulas
Range	Formula
B2:B16	B2	=UNIQUE(Table3[Project])
C2:C16	C2	=SUMIFS(Table3[Jan-22],Table3[Project],Summary!B2,Table3[Employee],TblProjAlloc[[#Headers],[Person 1]])
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rngProject	=Summary!$B$2:$B$9	C2

Cells with Data Validation
Cell	Allow	Criteria
N2	List	=rngMonth

I am using a Dynamic Range using =Sort(Unique for the projects so in the actual spreadsheet I am unable to create a table as it causes it to SPILL

 

[PaulyK]

If you mean that only one column shows totals & the others are all 0s then check that the names in the header row are exactly the same as those in the data table.

Thanks. Think it is probably something along those lines. The actual data source i am using is a little more complex than that in the example i provided (same principle) so am just working it out

 

[PaulyK]

This is where i got stuck....


Summary

Resource Allocation Tool - RAT - Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Project ID	Project	Mar-22	Person 1	Person 2	Person 3	Person 20	Person 13	Person 14	Person 8	Person 6	Person 15	Person 10	Person 26	Person 4	Person 7	Person 11	Person 9	Person 23	Person 18	Person 5	Person 27	Person 25	Person 12	Person 17	Person 16	Person 22	Person 19	Person 21
2		Project 1		5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3		Project 2		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4		Project 3		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5		Project 8		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6		Project 9		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7		Project 10		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8		Project 11		8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9		Project 13		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10		Project 14		10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11		Project 15		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	6	0	0	0	0	0
12		Project 16		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13		Project 17		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14		Project 18		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15		Project 19		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16		Project 20		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17		Project 21		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18		Project 22		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19		Project 23		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20		Project 24		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21		Project 25		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22		Project 26		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23		Project 27		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24		Project 28		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25		Project 29		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26		Project 30		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27		Project 31		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
28		Project 32		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29		Project 5		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30		Project 33		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
31		Project 34		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
32		Project 35		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33		Project 36		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
34		Project 37		3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
35		Project 39		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36		Project 40		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
37		Project 41		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
38		Project 42		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
39		Project 43		3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	7	0	0	0	0	0
40		Project 44		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
41		Project 45		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
42		Project 46		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
43		Project 47		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
44		Project 6		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
45		Project 4		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
46		Project 48		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
47		Project 50		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
48		Project 51		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
49		Project 52		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
50		Project 53		8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
51		Project 54		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
52		Project 55		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
53		Project 7		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
54		Project 56		1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
55		Project 57		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
56		Project 58		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
57		Project 59		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
58		Project 60		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
59		Project 61		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Summary

Cell Formulas
Range	Formula
D1:AC1	D1	=TRANSPOSE(rngPeople)
B2:B59	B2	=UNIQUE(TblProjAlloc[PROJECT NAME])
D2:D59,X2:X59,N2:N59	D2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[PROJECT NAME],$B2#,TblProjAlloc[WHO],D1)
E2:E59,Y2:Y59,O2:O59	E2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[WHO],$B2#,TblProjAlloc[Mar-22],E1)
F2:F59,Z2:Z59,P2:P59	F2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Mar-22],$B2#,TblProjAlloc[Apr-22],F1)
G2:G59,AA2:AA59,Q2:Q59	G2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Apr-22],$B2#,TblProjAlloc[May-22],G1)
H2:H59,AB2:AB59,R2:R59	H2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[May-22],$B2#,TblProjAlloc[Jun-22],H1)
I2:I59,AC2:AC59,S2:S59	I2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Jun-22],$B2#,TblProjAlloc[Jul-22],I1)
J2:J59,T2:T59	J2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Jul-22],$B2#,TblProjAlloc[Aug-22],J1)
K2:K59,U2:U59	K2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Aug-22],$B2#,TblProjAlloc[Sep-22],K1)
L2:L59,V2:V59	L2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[Sep-22],$B2#,TblProjAlloc[PROJECT NUMBER],L1)
M2:M59,W2:W59	M2	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($C$1,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[PROJECT NUMBER],$B2#,TblProjAlloc[PROJECT NAME],M1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Project allocation'!_FilterDatabase	='Project allocation'!$A$8:$C$38	L2:M2, V2:W2
rngPeople	=Lists!$H$2:$H$27	D1

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=rngMonths
D1:AD1	List	=IT_Team

 

[PaulyK]

Data - Allocation

Resource Allocation Tool - RAT - Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	PROJECT NUMBER	PROJECT NAME	WHO	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
2	190105	Project 1	Person 1	5	6	1	0	0	0	0
3	190105	Project 1	Person 2	1	1	0	0	0	0	0
4	190165	Project 2	Person 3	0.5	0	0	0			1
5	190165	Project 2	Person 20	0	0					2
6	190165	Project 2	Person 13	0	1					3
7	190165	Project 2	Person 14	0	0					4
8	200066	Project 3	Person 8	1	1			1.5
9	200067	Project 8	Person 6	0	0	0	0			1
10	200067	Project 8	Person 15	0	0	0	0
11	200067	Project 8	Person 3	2	0	0	0
12	200067	Project 8	Person 10	0	0	0	0	2.5
13	200067	Project 8	Person 26	0	0	0	0	2.5		1
14	200067	Project 8	Person 1	0	0	0	0	2.5
15	200078	Project 9	Person 4	0.5	2			2.5
16	200078	Project 9	Person 10	0.5	0.5			2.5	2.5	1
17	200087	Project 10	Person 20	4	8
18	210001	Project 11	Person 1	8	8	8	8	8	8	1
19	210001	Project 11	Person 15	14	14	14				1
20	210004	Project 13	Person 7			0	1	0	0	11
21	210004	Project 13	Person 11			5	4	2	1	1
22	210004	Project 13	Person 9			3	6	2	0	1
23	210004	Project 13	Person 23			1				1
24	210007	Project 14	Person 1	10	10	8				1
25	210007	Project 14	Person 18	8	4	1
26	210007	Project 14	Person 26		8	8
27	210007	Project 14	Person 5	4	4	3
28	210007	Project 14	Person 27	0	0	0	1.5		5	1
29	210007	Project 14	Person 9	0.5	0	0	1.5			1
30	210007	Project 14	Person 25	15	15	2	2		5
31	210007	Project 14	Person 4	0	0	0
32	210007	Project 14	Person 8	2	0	0
33	210007	Project 14	Person 23	0	0	0		3	4	1
34	210007	Project 14	Person 12	0	2	2
35	210008	Project 15	Person 3	1	0	0	0			1
36	210008	Project 15	Person 17	1	1	1				1
37	210008	Project 15	Person 6	1	1	1			11	1
38	210008	Project 15	Person 12	6	5	5
39	210010	Project 16	Person 2	8	8	10	10	8	4
40	210015	Project 17	Person 18	3			1
41	210022	Project 18	Person 15
42	210024	Project 19	Person 15
43	210028	Project 20	Person 6	0
44	210028	Project 20	Person 15	2
45	210036	Project 21	Person 18	4
46	210037	Project 22	Person 16
47	210044	Project 23	Person 3	1	1	0	0
48	210044	Project 23	Person 17	1	1	1
49	210055	Project 24	Person 18	7		7
50	210062	Project 25	Person 13	3	10	20	20	20	20	20
51	210062	Project 25	Person 23		10	20	11	4	1	1
52	210062	Project 25	Person 10		10	20	20	20	20	20
53	210062	Project 25	Person 27	1	10	20*	0	20*	20*	20
54	210066	Project 26	Person 3	0	0	0	0
55	210066	Project 26	Person 6	0	0
56	210066	Project 26	Person 18	0	0		7
57	210067	Project 27	Person 10	20	20	10
58	210070	Project 28	Person 10	8	8	8	8	6	6	6
59	210070	Project 28	Person 15	0.5	0.5	0.5	0.5	0.5	0.5	0.5
60	210070	Project 28	Person 1	0	0	0	0	0	0	0
61	210070	Project 28	Person 4	1	1	1	1	1	1	1
62	210070	Project 28	Person 2	3	0	0	0	0	0	0
63	210070	Project 28	Person 8	0	0	0	0	0	0	0
64	210070	Project 28	Person 6	0	0	0	0	0	0	0
65	210070	Project 28	Person 14	0	0	0	0	0	0	0
66	210074	Project 29	Person 9	5
67	210074	Project 29	Person 2	2
68	210074	Project 29	Person 3	4
69	210074	Project 29	Person 8	5
70	210082	Project 30	Person 17
71	210082	Project 30	Person 12
72	210082	Project 30	Person 18			7
73	210083	Project 31	Person 12			3	3	2	1
74	210083	Project 31	Person 11			4	4	4	2
75	210088	Project 32	Person 6	3		3	3	0	0
76	210089	Project 5	Person 2	4	4	4	0	0	0	0
77	210099	Project 33	Person 18
78	210113	Project 34	Person 18	0	0
79	210115	Project 35	Person 13
80	210115	Project 35	Person 17	10	7	4	1	1	1
81	210116	Project 36	Person 17	4	5	4	4	4	4
82	210138	Project 37	Person 9	4	4
83	210138	Project 37	Person 1	3	3		0	0	0	0
84	210138	Project 37	Person 10	0	10	10
85	210138	Project 37	Person 23		10	10
86	210138	Project 37	Person 2	4	4				5	5
87	210138	Project 37	Person 20	10	6
88	210141	Project 39	Person 5
89	210144	Project 40	Person 3	3
90	210145	Project 41	Person 25	2	2	2	2	7	8
91	210146	Project 42	Person 20	7	4
92	210147	Project 43	Person 12	7	7	7	7	7	7	7
93	210147	Project 43	Person 13	5	5	5	0	0	0	0
94	210147	Project 43	Person 20	2
95	210147	Project 43	Person 2	5	5	5	4	0	0	0
96	210147	Project 43	Person 1	3	4	4	4	4	0	0
97	210148	Project 44	Person 20	1	6
98	210154	Project 45	Person 18
99	210158	Project 46	Person 22	4
100	210158	Project 46	Person 4	14
101	210160	Project 47	Person 1
102	210162	Project 6	Person 25				11	7	4	4
103	210163	Project 4	Person 14	6	6	6
104	210164	Project 47	Person 25	17	1
105	210165	Project 48	Person 25	1	7	8	1	1	5.5	1.5
106	210166	Project 50	Person 16	9	10	8	2
107	210167	Project 51	Person 13	3	1
108	210167	Project 51	Person 10	5	3
109	210167	Project 51	Person 19	3	6	3	6	2
110	210168	Project 52	Person 23	5	19	22	11
111	210169	Project 53	Person 21							1
112	210169	Project 53	Person 1	8	8	6
113	210169	Project 53	Person 2	23	13
114	210169	Project 53	Person 14	23	13
115	210170	Project 54	Person 15	4	4	4	0	0	0	0
116	210170	Project 54	Person 2	2	4	4	4	0	0	0
117	210170	Project 54	Person 20	4	5		1		2	3
118	210171	Project 55	Person 20	2			5
119	210172	Project 7	Person 19	10	12	15	12	12	4
120	210172	Project 7	Person 27	18	9	9	6	6	2
121	210174	Project 56	Person 1	1	2	1	0	1	1	1
122	210174	Project 56	Person 20	1	3	3	1
123	210175	Project 57	Person 13	2	4	4	2
124	210177	Project 58	Person 19	3	5	5	5	1
125	210183	Project 59	Person 7	1	1	1	1	1
126	210184	Project 60	Person 27		6	7	11			1
127	210186	Project 61	Person 22		4	8	8	5
128	210186	Project 61	Person 23		8	10	7
129	210186	Project 61	Person 21		2	10	10	3
Project allocation

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B112:B114	Expression	=$B112="Awaiting PPM Upload"	text	NO
C2:C129	Expression	=COUNTBLANK(C2)	text	NO
C111:C112	Expression	=$B111="Awaiting PPM Upload"	text	NO
A111:B111,A112:A114,C112:C114,A115:C131,A2:C110	Expression	=$B2="Awaiting PPM Upload"	text	NO
D2:J129	Expression	=COUNTBLANK(D2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C112:C129	List	=IT_Team
C2:C110	List	=IT_Team

 

[Fluff]

You haven't locked the columns, so when you drag the formula across the table columns change.

 

[PaulyK]

You haven't locked the columns, so when you drag the formula across the table columns change.

Oh as simple as that? I can see now. I wasn’t aware you could lock a column (I know for a cell I can do Fn F4. How do I lock a Column?

 

[Fluff]

The same way I did

=SUMIFS(INDEX(Table3[[Jan-22]:[Dec-22]],,MATCH(TEXT($N$2,"mmm-yy"),Table3[[#Headers],[Jan-22]:[Dec-22]],0)),Table3[[Project]:[Project]],$B2#,Table3[[Employee]:[Employee]],C1)

 

[PaulyK]

The same way I did

=SUMIFS(INDEX(Table3[[Jan-22]:[Dec-22]],,MATCH(TEXT($N$2,"mmm-yy"),Table3[[#Headers],[Jan-22]:[Dec-22]],0)),Table3[[Project]:[Project]],$B2#,Table3[[Employee]:[Employee]],C1)

Genius! got it. thank you .

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[PaulyK]

The same way I did

=SUMIFS(INDEX(Table3[[Jan-22]:[Dec-22]],,MATCH(TEXT($N$2,"mmm-yy"),Table3[[#Headers],[Jan-22]:[Dec-22]],0)),Table3[[Project]:[Project]],$B2#,Table3[[Employee]:[Employee]],C1)

Thank you so much. It works! Easy when you know how!

 

[PaulyK]

One final question. In my data set I have another (separate) table of projects / people (these are ones awaiting approval) Is it possible to include this data in the main summary. Or potentially an easy way to sum both totals?