Aggregate values based on lookup IDs while meeting multiple criteria

Question

I am given sales data as per "sheet 1" below. Each row contains the quarter, the product, the region ID, and the sales figure. Each region can have multiple IDs, and I have a lookup table (sheet 2) which denote which region each ID belongs to.

My goal is to get to sheet 3. Essentially I am trying to write a formula that will reference the product name in column A, the quarter in cell A1 (which the user will input), and aggregate the relevant sales figures under each region in column B.

I have tried nesting an INDEX & MATCH function within a SUMIFS within a SUMPRODUCT as per below, but I am getting a #VALUE! error:

=SUMPRODUCT(SUMIFS(INDEX(sheet1!$D:$D$,MATCH(1,($A4=sheet1!$B:$B)*($A$1=Sheet1!$A:$A),0),0),sheet1$C:$C,sheet2!$A$2:$A$8)*(sheet2!$B$2:$B$8=$B4))

Does anyone know what is wrong with my formula, or if there is a better approach to this problem?

Sheet 1 (Raw data)

	A	B	C	D
1	Quarter	Product	ID	2021 Sales
2	Q1	A	1	39
3	Q1	A	3	41
4	Q1	A	7	20
5	Q1	A	14	7
6	Q1	A	25	2
7	Q1	A	27	2
8	Q1	A	44	45
9	Q1	B	1	28
10	Q1	B	3	34
11	Q1	B	7	29
12	Q1	B	14	48
13	Q1	B	25	5
14	Q1	B	27	15
15	Q1	B	44	32
16	Q2	A	1	19
17	Q2	A	3	28
	and so forth…

Sheet 2 (region ID lookup table)

	A	B
1	ID	Region
2	1	East
3	3	East
4	7	Central
5	14	Central
6	25	Central
7	27	West
8	44	West

Sheet 3 (Report)

	A	B	C
1	Q1
2
3	Product	Region	Sales
4	A	East	29
5	A	Central	42
6	A	West	31

Answer 1

A little rework:

 =SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!C:C,IF(Sheet2!$B$2:$B$8=$B4,Sheet2!$A$2:$A$8),Sheet1!A:A,$A$1,Sheet1!B:B,A4))

Depending on version one may need to confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

With the dynamic array formula FILTER we can replace the IF() part with FILTER():

=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!C:C,FILTER(Sheet2!$A$2:$A$8,Sheet2!$B$2:$B$8=$B4),Sheet1!A:A,$A$1,Sheet1!B:B,A4))

And it will save a few iterations.