Need formula to sum data using multiple parameters

[excel?]

I need to sum my data based by Class (Col A) for each pay type (Row 2) for each week (Row 1). I tried using SUMIFS [=SUMIFS($C$3:$H$30,$A$3:$A$30,100,$C$2:$H$2,"Reg",$C$1:$H$1,6/4/2023)] to sum the data in $C$3:$H$30 based on Class 100 in Col A and Reg in Row 2 and the Date 6/4/2023 in Row 1, but it just returns #VALUE!.

Is there a formula to get the totals for each Class for each pay type for each week without having to have a separate formula for each pay type/week?

Essentially, I would like a formula to fill the table below that updates each month when I change the dates.

Thank you in advance for any suggestions you can provide.

 

[alansidman]

Please re upload your data sample using XL2BB. Cannot manipulate data in a picture and really don't want to recreate what you already have.

 

[kweaver]

A picture isn't very helpful. If you use XL2BB to illustrate your sample data, that can be used in testing for a solution.

 

[kweaver]

Could you use this and expand the ranges accordingly?

021623 Misc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2			reg	ot	vac						reg	ot	vac
3	100		570.00	302						100	1890.00	1049.00	0.00
4	100		750.00							200	1322.83	650.00	216.00
5	100		570.00	747
6	200		506.83	178
7	200		816.00	472	216
Sheet12

Cell Formulas
Range	Formula
K3:M4	K3	=SUMIF($A$3:$A$7,$J3,C$3:C$7)

 

[excel?]

Please re upload your data sample using XL2BB. Cannot manipulate data in a picture and really don't want to recreate what you already have.

Thanks - not familiar with XL2BB so here goes:

formula help 06-16-23.xlsx
	A	B	C	D	E	F	G	H
1			6/4/2023	6/4/2023	6/4/2023	6/11/2023	6/11/2023	6/11/2023
2	Class	Name	Reg	OT	Vac	Reg	OT	Vac
3	100	Tom	570.00	$ 302		570.00	$ 189
4	100	James	750.00			750.00	$ 1,024
5	100	Heather	570.00	$ 747				570.00
6	200	Denise	506.83	$ 178		506.83	$ 510
7	200	Walter	816.00	$ 472	$ 216	816.00	$ 343
8	300	David	710.00	$ 1,092		710.00	$ 573
9	300	Joyce	723.61	$ 92	$ 198	723.61	$ 77
10	300	Jennifer	570.00					550.00
11	300	Tony	776.00			776.00	$ 399
12	400	Greg	900.00	$ 427		900.00	$ 564
13	400	Mike	794.82	$ 535		794.82	$ 525
14	500	Sara	1,238.00	$ 71		1,238.00
15	500	Tammy	882.80	$ 248		410.80		$ 472
16	500	Kristin	780.00	$ 892		780.00
17	600	Jill	570.00	$ 485		570.00
18	600	Matt	570.40	$ 258		570.40	$ 389
19	600	Ron	700.00	$ 999		700.00	$ 352
20	600	Bob	752.80	$ 749		752.80
21	700	Paul	723.60	$ 308		723.60
22	700	Linda			886.80	886.80
23	700	Alicia	894.80	$ 561		894.80	$ 347
24	800	Barbara	863.60	$ 358		863.60	$ 353
25	800	Steve	1,079.20	$ 751		1,079.20
26	800	Kyle			760.00	760.00	$ 50
27	900	Susan	576.55	$ 466		576.55	$ 193	$ 162
28	900	Lara	750.00	$ 6		750.00	$ 52	$ 133
29	900	Tyler	704.00	$ 217		704.00	$ 205	$ 114
30	900	Pete	85.78	$ 940		85.78
Sheet1

 

[kweaver]

Modifying my previous to relate to your data:

021623 Misc.xlsx
	J	K	L	M	N	O	P
1		6/4/2023	6/4/2023	6/4/2023	6/11/2023	6/11/2023	6/11/2023
2	Class	Reg	OT	Vac	Reg	OT	Vac
3	100	1890	1049	0	1320	1213	570
4	200	1322.83	650	216	1322.83	853	0
5	300	2779.61	1184	198	2209.61	1049	550
6	400	1694.82	962	0	1694.82	1089	0
7	500	2900.8	1211	0	2428.8	0	472
8	600	2593.2	2491	0	2593.2	741	0
9	700	1618.4	869	886.8	2505.2	347	0
10	800	1942.8	1109	760	2702.8	403	0
11	900	2116.33	1629	0	2116.33	450	409
Sheet13

Cell Formulas
Range	Formula
J3:J11	J3	=UNIQUE(A3:A30)
K3:P11	K3	=SUMIF($A$3:$A$30,$J3,C$3:C$30)
Dynamic array formulas.

 

[alansidman]

An alternative is with Power Query (Get and Transform Data)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type date}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Column1", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {":Class", ":Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {":Class", "Attribute"}, {{"Hours", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Hours")
in
    #"Pivoted Column"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8		:Class	6/4/2023:Reg	6/4/2023:OT	6/11/2023:Reg	6/11/2023:OT	6/11/2023:Vac	6/4/2023:Vac
2			6/4/2023	6/4/2023	6/4/2023	6/11/2023	6/11/2023	6/11/2023		100	1890	1049.66	1320	1212.79	570
3	Class	Name	Reg	OT	Vac	Reg	OT	Vac		200	1322.83	650.19	1322.83	853.21		215.84
4	100	Tom	570	302.31		570	188.85			300	2779.61	1184.35	2209.61	1048.74	550	198.24
5	100	James	750			750	1023.94			400	1694.82	961.98	1694.82	1089.28
6	100	Heather	570	747.35				570		500	2900.8	1210.73	2428.8		472
7	200	Denise	506.83	178.13		506.83	510.1			600	2593.2	2489.96	2593.2	740.57
8	200	Walter	816	472.06	215.84	816	343.11			700	1618.4	868.74	2505.2	346.95		886.8
9	300	David	710	1092.39		710	572.81			800	1942.8	1109.29	2702.8	402.17		760
10	300	Joyce	723.61	91.96	198.24	723.61	77.25			900	2116.33	1629.79	2116.33	450.71	408.88
11	300	Jennifer	570					550
12	300	Tony	776			776	398.68
13	400	Greg	900	426.57		900	564.19
14	400	Mike	794.82	535.41		794.82	525.09
15	500	Sara	1238	70.77		1238
16	500	Tammy	882.8	248.23		410.8		472
17	500	Kristin	780	891.73		780
18	600	Jill	570	484.76		570
19	600	Matt	570.4	257.86		570.4	388.8
20	600	Ron	700	998.67		700	351.77
21	600	Bob	752.8	748.67		752.8
22	700	Paul	723.6	308.19		723.6
23	700	Linda			886.8	886.8
24	700	Alicia	894.8	560.55		894.8	346.95
25	800	Barbara	863.6	358.43		863.6	352.63
26	800	Steve	1079.2	750.86		1079.2
27	800	Kyle			760	760	49.54
28	900	Susan	576.55	466.18		576.55	193.49	162.32
29	900	Lara	750	6.3		750	52.32	132.56
30	900	Tyler	704	217.31		704	204.9	114
31	900	Pete	85.78	940		85.78
Sheet1

 

[excel?]

Thank you for the help.