1

I have two table.

The first table contains menu and price:

Menu Price
Pizza 20
Pie 10
Beer 5
Burger 10

The second one contains a buyer and menu. I want to get the sum of price based on menu like in the example below on the Total

John Doe Niki
Pizza Burger Burger
Beer Beer Beer
Pie Beer
Total 25 35 20
Improve this question
1
  • The total for Doe is not right, it should be 25
    – Mayukh Bhattacharya
    Commented Apr 12 at 15:37

1 Answer 1

Reset to default
2

Try using SUM()/SUMPRODUCT() with SUMIFS()

enter image description here

• Formula used in cell E6

=SUM(SUMIFS($B3:$B6,$A3:$A6,E3:E5))

The above formula needs to be filled right.

Also if using MS365 could try using BYCOL()

=BYCOL(E3:G5,LAMBDA(α, SUM(SUMIF(A3:A6,α,B3:B6))))

You can use MMULT() with VLOOKUP() function as well:

=MMULT({1,1,1},IFERROR(VLOOKUP(E3:G5,A3:B6,2,0),0))

And one more way of doing this without LAMBDA() construct which needs to enable the Office Insiders in MS365 while writing this formula, using BYCOL():

=BYCOL(IFERROR(VLOOKUP(E3:G5,A3:B6,2,0),0),SUM)
Improve this answer
2
  • 1
    Thank you for your help, bro. I've already tried your formula, and it's working! I spent hours looking for this solution.
    – Jordan Ravelino
    Commented Apr 12 at 16:02
  • @JordanRavelino glad to know it worked for you. Thank you very much !!
    – Mayukh Bhattacharya
    Commented Apr 12 at 16:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .