Sumifs Array help

[lost_in_the_sauce]

I am trying to reference an array where I need to return a value in a cell if it matches 2 conditions in columns (A and B) and also a condition in the header row (row 2). I know SUMIFS cannot do this and it might be an issue for SUMPRODUCT or nested SUMS, but I have been unable to make it work. In the example below, for the second table, I want to pull values from the first table that match company (cell A1), category (Column A) and date (Row 2).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Company	Category	35	56	24	60	42	9	25	58
3	Amaze	Advertising	30	9	22	47	28	49	11	73
4	Beyond	Bonus	37	45	63	54	58	41	28	14
5	Cool	Advertising	46	0	78	22	11	76	17	0
6	Beyond	Legal	29	11	65	59	1	19	17	30
7	Amaze	Bonus	42	0	10	17	51	58	4	56
8	Amaze	Consulting	25	38	13	53	7	62	38	40
9	Amaze	Rebates	10	49	26	46	65	22	27	56
10	Cool	Bonus	78	13	0	45	10	48	40	47
11	Cool	Merchandise	6	34	76	2	55	75	25	52
12	Beyond	Advertising	73	25	57	16	71	23	48	29
13	Amaze	Fees	20	5	40	34	18	30	0	42
14	Amaze	Membership	14	69	33	55	33	41	37	74
15	Cool	Consulting	22	25	6	42	16	20	43	24
16	Beyond	Fees	57	28	3	78	68	3	72	10
17	Amaze	Merchandise	24	74	13	78	71	75	32	56
18	Cool	Legal	62	77	71	65	2	79	34	44
19	Beyond	Rebates	13	17	20	66	17	10	54	46
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Amaze
2			Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
3
4	Advertising
5	Bonus
6	Legal
7	Consulting
8	Rebates
9	Merchandise
10	Fees
11	Membership
Sheet2

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Amaze
2			31/01/2023	28/02/2023	31/03/2023	30/04/2023	31/05/2023	30/06/2023	31/07/2023	31/08/2023	30/09/2023	31/10/2023	30/11/2023	31/12/2023
3
4	Advertising		30	9	22	47	28	49	11	73	0	0	0	0
5	Bonus		42	0	10	17	51	58	4	56	0	0	0	0
6	Legal		0	0	0	0	0	0	0	0	0	0	0	0
7	Consulting		25	38	13	53	7	62	38	40	0	0	0	0
8	Rebates		10	49	26	46	65	22	27	56	0	0	0	0
9	Merchandise		24	74	13	78	71	75	32	56	0	0	0	0
10	Fees		20	5	40	34	18	30	0	42	0	0	0	0
11	Membership		14	69	33	55	33	41	37	74	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C4:N11	C4	=SUMIFS(INDEX(Sheet1!$C$3:$N$100,,XMATCH(TEXT(C$2,"mmm"),Sheet1!$C$1:$N$1)),Sheet1!$A$3:$A$100,$A$1,Sheet1!$B$3:$B$100,$A4)

 

[lost_in_the_sauce]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Amaze
2			31/01/2023	28/02/2023	31/03/2023	30/04/2023	31/05/2023	30/06/2023	31/07/2023	31/08/2023	30/09/2023	31/10/2023	30/11/2023	31/12/2023
3
4	Advertising		30	9	22	47	28	49	11	73	0	0	0	0
5	Bonus		42	0	10	17	51	58	4	56	0	0	0	0
6	Legal		0	0	0	0	0	0	0	0	0	0	0	0
7	Consulting		25	38	13	53	7	62	38	40	0	0	0	0
8	Rebates		10	49	26	46	65	22	27	56	0	0	0	0
9	Merchandise		24	74	13	78	71	75	32	56	0	0	0	0
10	Fees		20	5	40	34	18	30	0	42	0	0	0	0
11	Membership		14	69	33	55	33	41	37	74	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C4:N11	C4	=SUMIFS(INDEX(Sheet1!$C$3:$N$100,,XMATCH(TEXT(C$2,"mmm"),Sheet1!$C$1:$N$1)),Sheet1!$A$3:$A$100,$A$1,Sheet1!$B$3:$B$100,$A4)

Getting an N/A, even though I copied the formula over directly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	Company	Category	35	56	24	60	42	9	25	58
3	Amaze	Advertising	30	9	22	47	28	49	11	73
4	Beyond	Bonus	37	45	63	54	58	41	28	14
5	Cool	Advertising	46	0	78	22	11	76	17	0
6	Beyond	Legal	29	11	65	59	1	19	17	30
7	Amaze	Bonus	42	0	10	17	51	58	4	56
8	Amaze	Consulting	25	38	13	53	7	62	38	40
9	Amaze	Rebates	10	49	26	46	65	22	27	56
10	Cool	Bonus	78	13	0	45	10	48	40	47
11	Cool	Merchandise	6	34	76	2	55	75	25	52
12	Beyond	Advertising	73	25	57	16	71	23	48	29
13	Amaze	Fees	20	5	40	34	18	30	0	42
14	Amaze	Membership	14	69	33	55	33	41	37	74
15	Cool	Consulting	22	25	6	42	16	20	43	24
16	Beyond	Fees	57	28	3	78	68	3	72	10
17	Amaze	Merchandise	24	74	13	78	71	75	32	56
18	Cool	Legal	62	77	71	65	2	79	34	44
19	Beyond	Rebates	13	17	20	66	17	10	54	46
Sheet1

Cell Formulas
Range	Formula
D1:N1	D1	=EOMONTH(C1,1)

 

[lost_in_the_sauce]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Amaze
2			Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
3
4	Advertising		#N/A
5	Bonus
6	Legal
7	Consulting
8	Rebates
9	Merchandise
10	Fees
11	Membership
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUMIFS(INDEX(Sheet1!$C$3:$N$100,,XMATCH(TEXT(C$2,"mmm"),Sheet1!$C$1:$N$1)),Sheet1!$A$3:$A$100,$A$1,Sheet1!$B$3:$B$100,$A4)

 

[Fluff]

That's because you have changed the headers on sheet1 to dates rather than text.
Try

=SUMIFS(INDEX(Sheet1!$C$3:$N$100,,XMATCH(C$2,Sheet1!$C$1:$N$1)),Sheet1!$A$3:$A$100,$A$1,Sheet1!$B$3:$B$100,$A4)

 

[lost_in_the_sauce]

ooooooooohhhhh

I sure did. That seems to have made it work. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.