Multiple SUMIFS criteria range with multiple criterias

[Sinvictus]

Hi,

I am trying to sum up all the related allocated hours for the month, based on the project and person allocated.
In "Resources Mth" sheet is where all the information of the personnel's allocated project, duration and allocation percentage etc will reside. Here's the mini sheet:

Resource Tracking.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3	Name	Project	Start	End	Allocation	Hours	Status	3	4	5	6	7	8	9	10	11	12	1	2
4	CY	Test Project 1	21-Mar	30-Nov	50%	732	Initiate	36	84	88	88	84	92	88	84	88
5	CY	Test Project 2	1-Apr	22-Dec	10%	152	Initiate		17	18	18	17	18	18	17	18	13
6	JJ	Test Project 3	1-Apr	10-Oct	50%	548	Initiate		84	88	88	84	92	88	24
7	WY	Test Project 1	21-Mar	30-Nov	20%	293	Initiate	14	34	35	35	34	37	35	34	35
8	CY	Test Project 3	1-Apr	10-Oct	10%	110	Initiate		17	18	18	17	18	18	5
Resources Mth

Cell Formulas
Range	Formula
H4:S8	H4	=IFERROR($E4*8*(IF(MONTH($C4)=MONTH(H$2),NETWORKDAYS.INTL($C4,EOMONTH(H$2,0),1),IF(MONTH($D4)=MONTH(H$2),NETWORKDAYS.INTL(H$2,$D4),IF(OR($C4>H$2,$D4<H$2),"",NETWORKDAYS.INTL(H$2,EOMONTH(H$2,0)))))),"")
F4:F8	F4	=SUM(Table3[@[3]:[2]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:S8	Expression	=AND($D4>=H$2,$C4<I$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G4:G8	List	=Status

I would like to show the total allocated hours for the month in another sheet named "Check", starting from cell B5 onwards. Cell B1:B3 are user inputs:

Resource Tracking.xlsx
	A	B
1	Start	21-Mar
2	End	30-Nov
3	Personnel	CY
4
5	Mar 2022
6	Apr 2022
7	May 2022
8	Jun 2022
9	Jul 2022
10	Aug 2022
11	Sep 2022
12	Oct 2022
Check

Cell Formulas
Range	Formula
A5:A12	A5	=EDATE(EOMONTH($B$1,-1)+1,SEQUENCE(DATEDIF($B$1,$B$2,"M"),1,0))
Dynamic array formulas.

Hope to get pointed in the right direction for this!

 

[RasGhul]

Maybe this, convert your Resources Mth to a Table to keep formulas dynamic, Project name added as a User input criteria.

D & E Data Validation columns can be hidden or placed on their own tab;

Multiple SUMIFS criteria range_Sinvictus.xlsx
	A	B	C	D	E
1	Start	21/03/2022		Personel	Project
2	End	30/11/2022		CY	Test Project 1
3	Personnel	CY		JJ	Test Project 2
4	Project	Test Project 1		WY	Test Project 3
5
6	1/03/2022	732
7	Apr 2022	0
8	May 2022	0
9	Jun 2022	0
10	Jul 2022	0
11	Aug 2022	0
12	Sep 2022	0
13	Oct 2022	0
Check

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(Table1[Name])
E2:E4	E2	=UNIQUE(Table1[Project])
A6:A13	A6	=EDATE(EOMONTH($B$1,-1)+1,SEQUENCE(DATEDIF($B$1,$B$2,"M"),1,0))
B6:B13	B6	=SUMIFS(Table1[Hours],Table1[Start],">="&Check!A6,Table1[Start],"<="&EOMONTH(Check!A6,0),Table1[Name],Check!$B$3,Table1[Project],Check!$B$4)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Personel
B4	List	=Projects

Personel Named range fx

=Check!$D$2#

Projects Named range fx

=Check!$E$2#