Summarise result of Sumifs using different criteria

[PaulyK]

Hi,
I have created a Project Summary Resourcing Report (with thanks to @Fluff ) which uses SUMIFS to get a total of the days worked by a person, per month, per project. The month is selected via a dropdown list.

The problem is have is that in the main report there are upwards of 100 Projects and the same for People, which is then hard to summarise easily.
I would like to be able to select a specific person and show the projects they worked on and the days worked for that month (or overall) I thought I could achieve this via a dropdown list of People and then the data would show and would change based on month / person. Additionally if the same could be achieved Selecting the Project to get a list of people...but that isn't a priority)

I understand using an XLOOKUP I can display a whole column as a result, so wondered if this was an option to display the results, but not sure where to start; or if an easier way!

Summary would look something like this (or similar - but don't mind how it looks, just need it to do what i want)

Resource Allocation Tool - RAT.xlsx
	A	B	C	D
2	Name	Person 1
3	Date	May
4	Projects	Project 1	3
5		Project 2	3
6		Project 3	1.5
7		Project 6	5
8		Project 10	2
9
10		Total	14.5
Summary

Cell Formulas
Range	Formula
C10	C10	=SUBTOTAL(9,C4:C8)

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=IT_Team
B3	List	=rngMonths

Summary By Resource

Resource Allocation Tool - RAT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Resource Summary (Demand / Pipeline)
2	Select Period	May-22
3
4	Team	Resource	Project 1	Project 10	Project 11	Project 12	Project 13	Project 14	Project 15	Project 16	Project 2	Project 3	Project 4	Project 5	Project 6	Project 7	Project 8	Project 9	0
5	Team 1	Person 1	0	0	0	0	0	0	0	20	0	0	0	0	0	0	0	0	0
6	Team 1	Person 2	0	0	0	0	0	0	0	0	0	0	0	0	0	7.5	0	0	0
7	Team 2	Person 3	0	0	0	0	0	0	0	0	0	0	8	14	0	0	0	0	0
8	Team 1	Person 4	1	0	0	0	0	11	0	0	2	0	0	0	0	0	0	0	0
9	Team 2	Person 5	0	0	0	0	0	0	0	0	0	0	18	4	0	0	0	0	0
10	Team 3	Person 6	0	0	0	0	11	0	0	0	0	0	10	0	0	0	0	0	0
11	Team 1	Person 7	0	0	0	0	0	0	0	0	0	0	12	0	0	0	0	0	0
12	Team 2	Person 8	0	0	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	Team 2	Person 9	0	0	12.5	0	0	0	0	0	0	0	0	0	0	0	0	5	0
14	Team 2	Person 10	0.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	Team 4	Person 11	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	Team 5	Person 12	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
17	Team 4	Person 13	0	0	0	0	0	0	0	5	0	0	8	7	0	0	0	0	0
18	Team 1	Person 14	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0
19	Team 1	Person 15	0	0	0	0	0	0	0	0	0	0	1.5	20	0	0	0	0	0
20	Team 1	Person 16	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	Team 5	Person 17	0	0	0	0	0	0	0	0	0	0	0	2	0	0	10	0	0
22	Team 1	Person 18	0	0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0
23	Team 2	Person 19	0	0	0	16	0	0	0	0	0	3	0	0	0	0	0	0	0
24	Team 2	Person 20	0	0	0	0	0	0	0	15	0	0	0	0	0	0	0	0	0
Summary (Pipeline) by Resource

Cell Formulas
Range	Formula
C4:S4	C4	=TRANSPOSE(Lists!H2#)
B5:B24	B5	=UNIQUE(Lists!G2:G21)
C5:S24	C5	=SUMIFS(INDEX(TblNewProjAlloc[[Jan-22]:[Sep-22]],,MATCH(TEXT($B$2,"mmm-yy"),TblNewProjAlloc[[#Headers],[Jan-22]:[Sep-22]],0)),TblNewProjAlloc[[WHO]:[WHO]],$B5#,TblNewProjAlloc[[PROJECT NAME]:[PROJECT NAME]],C4)
A5:A20,A22:A24	A5	=IFERROR(INDEX(NameTbl[Team],MATCH('Summary (Pipeline) by Resource'!B5,NameTbl[Name],0))," ")&""
A21	A21	=IFERROR(INDEX(NameTbl[Team],MATCH('Summary (Pipeline) by Resource'!B21,NameTbl[Name],0))," ")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Project allocation (Pipeline)'!IT_Team	=NameTbl[Name]	A5:A24
'Summary - Pipeline (Days)'!IT_Team	=NameTbl[Name]	A5:A24
'Summary (Pipeline) by Resource'!IT_Team	=NameTbl[Name]	A5:A24
IT_Team	=NameTbl[Name]	A5:A24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:S24	Cell Value	>0	text	NO
C5:S5	Cell Value	>0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=rngMonths

Source Data

Resource Allocation Tool - RAT.xlsx
	A	B	C	D	E		G	H	I	J	K	L
2	STAGE	PROJECT NAME	WHO	Jan-22	Feb-22		Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
3	Demand	Project 1	Person 8						4	4	4	2
4	Demand	Project 1	Person 18				3		2	2	2	1
5	Demand	Project 1	Person 10				5	0.5	12	12	12	2
6	Demand	Project 1	Person 1				5		4	4	4	2
7	Demand	Project 1	Person 11					5	5	5	5	5
8	Demand	Project 1	Person 16				5		5	5	0	0
9	Demand	Project 1	Person 4					1	1	1
10	Demand	Project 1	Person 2				5		0	1	0	0
11	Demand	Project 2	Person 4		1		10	2	2	2	2
12	Demand	Project 3	Person 14		2		3	4	2
13	Demand	Project 3	Person 19				3	3
14	Demand	Project 3	Person 12				1	1	1.5	1.5	1.5	3
15	Pipeline	Project 4	Person 13					8	8	8	8	8
16	Pipeline	Project 4	Person 3					8	8	8	8	8
17	Pipeline	Project 4	Person 15					1.5	12	12	12	10
18	Pipeline	Project 4	Person 5		7.5			18	20	20	20	20
19	Pipeline	Project 4	Person 6		7.5			10
20	Pipeline	Project 4	Person 7		7.5			12
21	Pipeline	Project 5	Person 13		7.5			7		0.5
22	Pipeline	Project 5	Person 3		7.5		1.5	14		0.5
23	Pipeline	Project 5	Person 15		7.5			20		0.5
24	Pipeline	Project 5	Person 5		14		2	4
25	Pipeline	Project 5	Person 17					2
26	Pipeline	Project 6	Person 13		1				5	2	2	2
27	Pipeline	Project 7	Person 2				5	7.5	11	11	11	11
28	Pipeline	Project 8	Person 17				0.5	10	10	10	10
29	Pipeline	Project 9	Person 9		9			5	5	7.5
30	Pipeline	Project 10	Person 20
31	Pipeline	Project 11	Person 8				4	4	4	4	4
32	Pipeline	Project 11	Person 9		9			12.5		5.5	5	5
33	Pipeline	Project 12	Person 19				0.5	16	20	20	10	10
34	Pipeline	Project 13	Person 6				0.5	11	11	11	7.5	7.5
35	Pipeline	Project 14	Person 4				0.5	11	11	11	7.5	7.5
36	Pipeline	Project 15	Person 18		9			10	10	10	10	10
37	Pipeline	Project 16	Person 1		11			20	16	16	16	16
38	Pipeline	Project 16	Person 13		11			5	5	5	5	5
39	Pipeline	Project 16	Person 20		11			15	9	8	11
40
Project allocation (Pipeline)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:L40	Expression	=$A3="Pipeline"	text	NO
A3:L40	Expression	=$A3="Demand"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C3:C39	List	=IT_Team

 

[PaulyK]

Not sure ifthe Title of this matches the request....

 

[PaulyK]

Anyone?

 

[PaulyK]

I just need an easy way of being able to Summarise a large set of data.... I am sure the solution is simple but I can't work it out