SUMPRODUCT with 3 Criteria, index match by 1 Row & 2 Column

Question

Please help me..

I have a table that I want to SUM with condition of multiple criteria by 1 row & 2 column.

The Formula:

Column that should be result of SUM:

expected result:

I Have tried some formula but it just sum of first column, i need to sum of all numbers of table "I10:N47".

here my code below:

=IFNA(SUMPRODUCT(--(Sheet1!$H$10:$H$47=Sheet2!$B6)*(INDEX(Sheet1!$I$10:$N$47,,MATCH(Sheet2!$C$2,Sheet1!$I$5:$N$5,0))):(INDEX(Sheet1!$I$10:$N$47,,MATCH(Sheet2!$C$3,Sheet1!$I$6:$N$6,0)))),0)

thanks for the support..

Answer 1

Here is a small example you may be able to adjust to your ranges:

Formual in G4:

=SUMPRODUCT((A2:A6=G1)*((B1:D1=G2)+(B1:D1=G3))*(B2:D6))

Or applying some SUMIFS logic:

=SUMPRODUCT(SUMIFS(INDEX(B:B,MATCH(G1,A:A,0)):INDEX(D:D,MATCH(G1,A:A,0)),B1:D1,G2:G3))

---

_Edit1:

As per your comment, there can be same headers:

Formula in H4:

=SUMPRODUCT((A2:A6=H1)*((B1:E1=H2)+(B1:E1=H3))*(B2:E6))

---

_Edit2:

After your last comment I see you have several lines of headers that need to match up, try to adjust the below:

Formula in G4:

=SUMPRODUCT((A3:A7=H1)*(B1:E1=H2)*(B2:E2=H3)*(B3:E7))