Can someone kindly help with the following please, I would like to calculate the sum for each person if it says yes in the columns with the corresponding number
| Person | Car | House | Job | Holiday | Sum |
|---|---|---|---|---|---|
| 1 | Yes | No | Yes | No | |
| 2 | No | Yes | No | No |
| Item | Assigned Number |
|---|---|
| Car | 3 |
| House | 6 |
| Job | 5 |
| Holiday | 4 |
Try using SUM() or SUMPRODUCT()
• Formula used in cell F2
=SUM((B2:E2="Yes")*(B$1:E$1=Itemtbl[Item])*Itemtbl[Assigned Number])
The above formula needs to copy down! Also consider using Structured References aka Tables for the Item Range Data, like I did, I have named it as Itemtbl. This makes easier to work, and formulas also gets quickly updated when there is new row added.
If you have access to LAMBDA() can use BYROW() which spills the formula for the whole range therefore you dont have to copy down for the other cells:
=BYROW(B2:E3,LAMBDA(α, SUM((α="Yes")*(B1:E1=Itemtbl[Item])*Itemtbl[Assigned Number])))
Another way:
=SUM(XLOOKUP(B$1:E$1,Itemtbl[Item],Itemtbl[Assigned Number])*(B2:E2="Yes"))
