I am looking for help to sum values in a table using a mapping table.. Tried Xlookup and sumif. Not sure where I am going wrong.
Simplifying it in an example
Basically, I have two tables.
| Food type | Food |
|---|---|
| Fruits | Apple |
| Fruits | Orange |
| Vegetable | Spinach |
| Vegetable | Fenugreek |
| Food | Cost |
|---|---|
| Apple | $10 |
| Orange | $15 |
| Spinach | $20 |
| Fenugreek | $10 |
So, if my input is Foodtype = Fruits, then I should get an answer of $25 (as it will sum up Apple and Orange)..
The combination of sumifs and xlookup is only giving one answer ($10) whereas I need $25. please let me know. I cannot combine the tables, as I have to use the first mapping table in multiple places. Thankyou!
CodePudding user response：
You can try:
=SUM(SUMIF(E2:E5,FILTER(B2:B5,A2:A5=I2),F2:F5))
CodePudding user response：
This uses XLOOKUP and SUM.
=SUM(XLOOKUP(FILTER($B$2:$B$11,$A$2:$A$11=G2),$D$2:$D$5,$E$2:$E$5))
