Combining two data tables in Sumifs Project Summary Report

[PaulyK]

Hello,

I have created a Summary sheet (thanks to the excellent solutions / guidance from @Fluff ) which counts the number of Days worked, Per Approved Project and per Month. The data is pulled from a specific separate Data table. There is an additional Data set (similar in format) that I also need to reference / sum from; which is for unapproved Projects. Is there a way of combining the two sets of data to give a total of days? Presently the two data sets have to remain separate (so I can't put them all in one table)

Also, Is there a way of the table only showing Columns with Data in (not 0)

If anyone feels there is an easier way to achieve the end goal, I am all ears!!

Paul

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
3	Period	Apr-22
4
5	Project ID	Project	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	0	0
6		Project 1	6	1	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 2	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8		Project 3	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9		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
10		Project 9	0	0	0	0	0	0	0	0	0	0.5	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0
11		Project 10	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12		Project 11	8	0	0	0	0	0	0	0	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13		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
14		Project 14	10	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	4	4	0	15	2	0	0	0	0
15		Project 15	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	5	1	0	0	0
16		Project 16	0	8	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 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
18		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
19		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
20		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
21		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
22		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
23		Project 23	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
24		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
25		Project 25	0	0	0	0	10	0	0	0	0	10	0	0	0	0	0	10	0	0	10	0	0	0	0	0	0
Summary

Cell Formulas
Range	Formula
C5:AB5	C5	=TRANSPOSE(rngPeople)
B6:B25	B6	=UNIQUE(TblProjAlloc[PROJECT NAME])
C6:AA25	C6	=SUMIFS(INDEX(TblProjAlloc[[Mar-22]:[Sep-22]],,MATCH(TEXT($B$3,"mmm-yy"),TblProjAlloc[[#Headers],[Mar-22]:[Sep-22]],0)),TblProjAlloc[[PROJECT NAME]:[PROJECT NAME]],$B6#,TblProjAlloc[[WHO]:[WHO]],C5)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rngPeople	=Lists!$H$2:$H$27	C5

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

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=rngMonths
C5:AA5	List	=IT_Team

Data - Project 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
55
56
57	Demand / Pipeline Projects (Not yet approved)
58	Stage	PROJECT NAME	WHO	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
59	Demand	NewDemandProject1	Person 1	1			2	4	4	4
60	Demand	NewDemandProject1	Person 2		1	3	1	2	2	2
61	Demand	NewDemandProject1	Person 4			3	2	12	12	12
62	Demand	NewDemandProject2	Person 1			3	2	4	4	4
63	Demand	NewDemandProject2	Person 2			1	5	0	0	5
64	Demand	NewDemandProject2	Person 3		2		0	5	5	0
65	Demand	NewDemandProject3	Person 2	1	2		0	0.5	0.5	0
66	Demand	NewDemandProject3	Person 4				0	0.5	0.5	0
67	Demand	NewDemandProject3	Person 6	1	2		4	4	4
68	Demand	NewDemandProject4	Person 1		1	4	14	14	14	1
69	Demand	NewDemandProject4	Person 10	1			10	10	10	2
70	Demand	NewDemandProject4	Person 11		1		14	10	10
71	PipeLine	NewProject1	Person 1			4	8	8	8	2
72	PipeLine	NewProject1	Person 4	1	1	4	10	10	10	2
73	PipeLine	New Project2	Person 2	1.5		4	20	20	20
74	PipeLine	New Project3	Person 7	0		1		1.5	1
75	PipeLine	New Project4	Person 4	1	2	2				1
76	PipeLine	New Project4	Person 10	0.5				1.5	1.5	1.5
77	PipeLine	New Project5	Person 6	5	0	0	3	2
78	PipeLine	New Project5	Person 7	5		5		4		4
79	PipeLine	New Project6	Person 20	14	0	4	12	4	7	7
Project allocation

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D59:J79	Expression	=COUNTBLANK(D59)	text	NO
A78:B79	Expression	=$A78="Pipeline"	text	NO
A78:B79	Expression	=$A78="Demand"	text	NO
B78:B79	Expression	=COUNTBLANK(B78)	text	NO
A76:B77	Expression	=$A76="Pipeline"	text	NO
A76:B77	Expression	=$A76="Demand"	text	NO
B76:B77	Expression	=COUNTBLANK(B76)	text	NO
A59:C75,C76:C79	Expression	=$A59="Pipeline"	text	NO
A59:C75,C76:C79	Expression	=$A59="Demand"	text	NO
A57:H57	Expression	=$B57="Awaiting PPM Upload"	text	NO
C2:C53,B59:C75,C76:C79	Expression	=COUNTBLANK(B2)	text	NO
A2:C55	Expression	=$B2="Awaiting PPM Upload"	text	NO
D2:J53	Expression	=COUNTBLANK(D2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C53	List	=IT_Team
C59:C79	List	=IT_Team

 

[PaulyK]

The 2nd range of Data is Summarised in the below Table (which Matches the Summary) but I would like to be able to combine them

Pipeline Project 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
3	Period	Aug-22
4
5	Project ID	Project	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	0
6		NewDemandProject1	4	2	0	0	0	0	0	0	0	0	0	12	0	0	0	0	0	0	0	0	0	0	0	0
7		NewDemandProject2	4	0	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8		NewDemandProject3	0	0.5	0	0	0	0	0	4	0	0	0	0.5	0	0	0	0	0	0	0	0	0	0	0	0
9		NewDemandProject4	14	0	0	0	0	0	0	0	0	10	0	0	0	10	0	0	0	0	0	0	0	0	0	0
10		NewProject1	8	0	0	0	0	0	0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0
11		New Project2	0	20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12		New Project3	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
13		New Project4	0	0	0	0	0	0	0	0	0	1.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14		New Project5	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		New Project6	0	0	0	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16
Summary (Pipeline)

Cell Formulas
Range	Formula
C5:AB5	C5	=TRANSPOSE(rngPeople)
B6:B15	B6	=UNIQUE(Table4[PROJECT NAME])
C6:Z15	C6	=SUMIFS(INDEX(Table4[[Mar-22]:[Sep-22]],,MATCH(TEXT($B$3,"mmm-yy"),Table4[[#Headers],[Mar-22]:[Sep-22]],0)),Table4[[PROJECT NAME]:[PROJECT NAME]],$B6#,Table4[[WHO]:[WHO]],C5)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rngPeople	=Lists!$H$2:$H$27	C5

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

Cells with Data Validation
Cell	Allow	Criteria
C5:Z5	List	=IT_Team
B3	List	=rngMonths