Sum Multiple Row and Column with Condition

[Chang123]

Hey there, I have two sheets on hand, and need to sum the multiple results in condition at the third sheet. Are there anyone can tell me the formula that can achieve it? Thanks!

Here's what I have:

Sheet 1

Sheet 2

And here's what I want:

Sheet 3

 

[navic]

If I understood you well, try the formula below on Sheet3

=SUMPRODUCT((Sheet1!$B$1:$D$1=$A2)*(Sheet1!$A$2:$A$4=B$1)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$7,Sheet2!$A$2:$A$7,$A2)

 

[Chang123]

If I understood you well, try the formula below on Sheet3

=SUMPRODUCT((Sheet1!$B$1:$D$1=$A2)*(Sheet1!$A$2:$A$4=B$1)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$7,Sheet2!$A$2:$A$7,$A2)

Thank you navic, and let me make it more clear.

This should be how the Sheet 3 looks like:

And with new rows being added to Sheet 2, the amount in Sheet 3 should also be updated accordingly.

Thanks again

 

[navic]

And with new rows being added to Sheet 2, the amount in Sheet 3 should also be updated accordingly.

Probably a shorter formula is possible, but here's a quick one (three column headers)
Try

=SUMPRODUCT((Sheet1!$B$1:$D$1="A")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"A")+SUMPRODUCT((Sheet1!$B$1:$D$1="B")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"B")+SUMPRODUCT((Sheet1!$B$1:$D$1="C")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"C")

 

[Chang123]

Thank you, and can I ask what the A, B and C here in red mean? because when I copy the formula to my workbook, the results just showing 0s. I don't know where's the issue and I'm not sure if it's about the A, B and C here...

=SUMPRODUCT((Sheet1!$B$1:$D$1="A")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"A")+SUMPRODUCT((Sheet1!$B$1:$D$1="B")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"B")+SUMPRODUCT((Sheet1!$B$1:$D$1="C")*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,"C")

 

[navic]

The A, B and C are your headers on Sheet1 (Row1).

Instead of these letters, you can put the cell address in the formula (like formula below).

=SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$B$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$B$1)+SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$C$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$C$1)+SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$D$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$D$1)

Note:
- "A" -> Sheet1!$B$1
- "B" -> Sheet1!$C$1
- "C" -> Sheet1!$D$1

 

[Chang123]

The A, B and C are your headers on Sheet1 (Row1).

Instead of these letters, you can put the cell address in the formula (like formula below).

=SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$B$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$B$1)+SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$C$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$C$1)+SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet1!$D$1)*(Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$D$4))*SUMIFS(Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100,Sheet1!$D$1)

Note:
- "A" -> Sheet1!$B$1
- "B" -> Sheet1!$C$1
- "C" -> Sheet1!$D$1

Got it, thanks!