Summarising Master sheet data into a summary sheet

[Manexcel]

I am using Windows 10 with Excel 2019.

I have a number of categories that I would like to summarise on a separate sheet based upon the 'master sheet'. Each category is based upon a month. The summary would be a sum of the 12 months creating a yearly sum and / or total to date. i.e. 2021, 2022, 2023...etc

I have been trying to use the formula =sumifs but unable to overcome with multiple criteria ranges and criterion.


If you can assist it would be greatly appreciated and I thank you for your time and consideration.

Summarising Data v 0.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	I am using Windows 10 with Excel 2019.
2
3	1) I have a number of categories that I would like to summarise on a separate sheet based upon the 'master sheet'. This would be a Yearly sum and / or total to date. 2) The dates in row 5 are used elsewhere in the workbook 3) DD cat 1 and 2 amounts are generally fixed amounts 4) YB cat 3 and 4 . The plan amount is the same each month but the actual amount only occurs when the invoice arrives 5) I have been trying to use the formula =sumifs but unable to overcome with multiple criteria ranges and criterion. 6) The sheet below is the 'master' sheet.
4
5	MASTER SHEET	###############		01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/01/2022	01/02/2022	01/03/2022	01/04/2022	01/05/2022	01/06/2022	01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023
6				Jan 21	Feb 21	Mar 21	Apr 21	May 21	Jun 21	Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23	Jun 23
7			Plan	70.00	490.00	490.00	490.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00	70.00
8			Actual	25.00	235.00	235.00	235.00	25.00	45.00	25.00	25.00	25.00	25.00	25.00	45.00	25.00	25.00	65.00	25.00	25.00	45.00
9			Diff.	45.00	255.00	255.00	255.00	45.00	25.00	45.00	45.00	45.00	45.00	45.00	25.00	45.00	45.00	5.00	45.00	45.00	25.00
10
11		Category
12		DD Cat 1	Plan	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00
13			Actual	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00
14
15		DD Cat 2	Plan	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00
16			Actual	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00
17
18		YB Cat 1	Plan	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00
19			Actual			40.00												40.00
20			Accum	20.00	40.00	20.00	40.00	60.00	80.00	100.00	120.00	140.00	160.00	180.00	200.00	220.00	240.00	220.00	240.00	260.00	280.00
21
22		YB Cat 2	Plan	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00	25.00
23			Actual						20.00						20.00						20.00
24			Accum	25.00	50.00	75.00	100.00	125.00	130.00	155.00	180.00	205.00	230.00	255.00	260.00	285.00	310.00	335.00	360.00	385.00	390.00
25
26
27	What I would like to happen - Part 1
28	To create a summary sheet based on; 1) To sum each DD cat for the year/ sum to date. Based on each months (Plan and Actual) amounts 2) Based on the text on the 'master sheet' in cells B12:B24 3) My concern is also how to overcome if DD cat 1 or 2 name is changed or moves cell location in the 'master sheet'
29	SUMMARY SHEET
30
31	DD categories
32					2021	2022	2023
33	DD Cat 1		Plan		60.00	60.00	60.00
34			Actual		60.00	60.00	60.00
35	DD cat 2		Plan		90.00	90.00	90.00
36			Actual		90.00	90.00	90.00
37
38	What I would like to happen - Part 2
39	To create a summary sheet based on; 1) To sum each YY cat for the year / sum to date. Based on each months (Plan / Actual / Accum ) amounts 2) based on the text in cells B12:B24 on the 'master sheet' 3) To keep a running accumaltive total for each YB cat 1 and 2. This will go over each year end and accumulate 4) My concern is also how to overcome if YB cat 1 or 2 name is changed or moves cell location in the 'master sheet'
40
41	YB categories
42					2021	2022	2023
43	YB Cat 1		Plan		120.00	120.00	120.00
44			Actual		40.00	-	40.00
45			Accum		80.00	200.00	280.00
46	YB cat 2		Plan		150.00	150.00	150.00
47			Actual		20.00	20.00	20.00
48			Accum		130.00	260.00	390.00
49
Sheet1

Cell Formulas
Range	Formula
B5	B5	=NOW()
Q5:U5,K5:O5,E5:I5	E5	=DATE(YEAR(D5),MONTH(D5)+1,DAY(D5))
D6:U6	D6	=TEXT(D5,"mmm yy")
D7:U8	D7	=SUMIF($C$11:$C$9435,$C7,D$11:D$9435)
D24,D20,D9:U9	D9	=D7-D8
E24:U24,E20:U20	E20	=E18-E19+D20

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:U6	Expression	=AND(MONTH($B$2)=MONTH(D$2),YEAR($B$2)=YEAR(D$2))	text	NO