Search code examples
excelexcel-formulasum

Sum Multiple Criteria from Columns and Rows

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:

enter image description here

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

Solution

  • You could use:

    enter image description here

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