Sum Multiple Criteria from Columns and Rows-CodePudding

I'm having some difficulties in Excel when trying to sum up cells by several criteria in columns and rows. The data would look like this:

For example, I want to sum column with criteria "I" and "L" together for row criteria "A" and "E" then it would be 101.

I try to use the formula below but this doesn't work and turns out to be N/A

=SUMIFS(INDEX('Sheet2'!$F$31:$BC$57;0;MATCH(Sheet1!B3&Sheet1!B2;'Sheet2'!$F$3:$AO$4;0));'Sheet2'!$E$31:$E$57;Sheet1!A4)

Thanks in advance

CodePudding user response：

You could use:

=SUMPRODUCT((C1:E1=I1)*(C2:E2=I2)*(A3:A6=G3)*(B3:B6=H3)*C3:E6)

CodePudding user response：

If you would've wanted it to work regardless of the position of the search terms in the row or column headers* you could use the following:

=SUM((MMULT(COUNTIF(A13:B13,A3:B6),COUNTIF(A10:B10,C1:E2))=2)*C3:E6)

*Note the switched letters L and I and A and Bgiving the expected result