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

 

[Fluff]

If your summary sheet is a table convert it to a normal range & you can use

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Project	Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10		Month
2		Project 1	2	6	20	0	0	2	0	0	0	0		01/01/2022
3		Project 2	2	0	2	0	0	0	0	0	0	0
4		Project 3	0	0	4	0	0	0	0	0	0	0
5		Project 4	0	12	0	0	0	0	0	0	0	0
6		Project 5	1	0	0	0	0	0	0	0	0	0
7		Project 6	0	0	0	0	0	0	1	0	0	0
8		Project 7	1	0	0	0	20	3	0	0	0	0
9		Project 8	2	0	0	0	0	0	0	0	0	0
10		Project 9	0	0	3	0	0	0	0	5	0	0
11		Project 10	6	0	0	0	0	0	0	0	0	0
12		Project 11	0	0	0	2	0	0	0	0	0	0
13		Project 12	0	0	0	3	0	0	0	0	0	0
14		Project 13	0	0	0	0	0	0	0	0	0	0
15		Project 14	0	0	0	0	1	0	0	0	0	0
16		Project 15	0	0	0	0	0	0	0	1	0	0
Master

Cell Formulas
Range	Formula
B2:B16	B2	=UNIQUE(Table3[Project])
C2:L16	C2	=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)
Dynamic array formulas.

 

[kweaver]

I think there's a conflict with Project 3 on your data table because you have Person 3 twice.

 

[Fluff]

Another option that will spill down & across

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Project	Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10		Month
2		Project 1	2	6	20	0	0	2	0	0	0	0		01/01/2022
3		Project 2	2	0	2	0	0	0	0	0	0	0
4		Project 3	0	0	4	0	0	0	0	0	0	0
5		Project 4	0	12	0	0	0	0	0	0	0	0
6		Project 5	1	0	0	0	0	0	0	0	0	0
7		Project 6	0	0	0	0	0	0	1	0	0	0
8		Project 7	1	0	0	0	20	3	0	0	0	0
9		Project 8	2	0	0	0	0	0	0	0	0	0
10		Project 9	0	0	3	0	0	0	0	5	0	0
11		Project 10	6	0	0	0	0	0	0	0	0	0
12		Project 11	0	0	0	2	0	0	0	0	0	0
13		Project 12	0	0	0	3	0	0	0	0	0	0
14		Project 13	0	0	0	0	0	0	0	0	0	0
15		Project 14	0	0	0	0	1	0	0	0	0	0
16		Project 15	0	0	0	0	0	0	0	1	0	0
17
Master

Cell Formulas
Range	Formula
B2:B16	B2	=UNIQUE(Table3[Project])
C2:L16	C2	=MMULT(--(B2#=TRANSPOSE(Table3[Project])),(C1:L1=Table3[Employee])*INDEX(Table3[[Jan-22]:[Dec-22]],,MATCH(TEXT($N$2,"mmm-yy"),Table3[[#Headers],[Jan-22]:[Dec-22]],0)))
Dynamic array formulas.

 

[PaulyK]

well spotted, thanks

 

[Fluff]

Did either of the solutions work for you?

 

[PaulyK]

Did either of the solutions work for you?

Thank you very much they did indeed, very pleased. I am now trying to recreate the formula in the actual workbook i use. ?


I've never come across =MMULT before in the 2nd example and not sure i could recreate it. so I am going with the 1st as it (just about) makes sense and i can understand it. (Would it be possible though to explain the logic a bit? I've just recently taught myself Sumifs and am attempting to replicate this

 

[Fluff]

This part is finding the value of N2 (in mmm-yy format) in the header of table3 & returning the entire column.

INDEX(Table3[[Jan-22]:[Dec-22]],,MATCH(TEXT($N$2,"mmm-yy"),Table3[[#Headers],[Jan-22]:[Dec-22]],0))

Then this is finding the project number that relates to the values in the B2 spill range (you could remove the # sign after B2 & then drag the formula dow)

Table3[[Project]:[Project]],$B2#

& the last part is matching the persons name.
HTH

 

[PaulyK]

That’s really helpful thank you. I’m struggling a little to replicate it in my spreadsheet (seems to only display one person’s days worked rather than everyone but I will persevere to get it right as it clearly works perfectly in the example

 

[Fluff]

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.