Dynamic Sumifs Formula: Multiple Row Criteria, Dynamic Column

[Bkisley]

I have a data table say A1:N100
Column A is Category
Column B is Sub Category
Columns C:N would be Jan - Dec

I need a dynamic sum that only sums up if Category A value is "Criteria1", Category B value is "Criteria2" and for only the month of my choosing. Sometimes this will be current month, sometimes will be prior month.
If it helps, let's put the month I need over in O1

Clearly this is a normal sumifs at first but cant figure out the column aspect.

=Sumifs(Dynamic Column Reference,A:A,"Criteria1",B:B,"Criteria2"

Hope this makes sense!

 

[kevin9999]

It gets a bit messy if you put the month criteria right next to the table (assuming it's an actual Excel table & not just a range) because whenever you change the value the table is resized to incorporate column O into itself. If it's just a range, then the following is one option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	HDR1	HDR2	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Feb
2	Criteria1	Criteria2	4	10	5	5	8	5	2	6	1	10	2	10
3	Criteria1	Criteria2	3	4	5	7	7	8	7	2	2	2	3	7
4	Criteria1	Criteria2	7	1	4	1	10	10	7	8	4	5	9	1
5	Criteria1	CriteriaX	3	9	2	5	5	10	1	1	5	2	3	8
6	Criteria1	CriteriaX	5	8	1	10	8	10	6	10	1	7	9	4
7	Criteria1	CriteriaX	3	2	6	1	5	10	1	7	10	4	10	9
8	CriteriaX	CriteriaX	9	1	8	5	10	9	7	2	5	4	2	5
9	CriteriaX	CriteriaX	6	4	10	7	9	5	4	8	1	7	7	1
10	CriteriaX	CriteriaX	7	1	4	6	2	9	9	2	5	7	9	8
11	CriteriaX	CriteriaX	3	2	7	6	4	4	6	2	2	10	1	10
12
13		15	= Sum of O1 month
14			Criteria1 & Criteria2
Sheet1

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT(($A$2:$A$11="Criteria1")*($B$2:$B$11="Criteria2")*($C$1:$N$1=O1),$C$2:$N$11)

Cells with Data Validation
Cell	Allow	Criteria
O1	List	=$C$1:$N$1

 

[Bkisley]

I can make this work - Thank you Kevin for the quick response!

 

[kevin9999]

I can make this work - Thank you Kevin for the quick response!

Happy to help and thanks for the feedback