I have a table that accounts for certain items a player (P1, P2, etc) in a game has -
| name | P1 | P2 | P3 | P4 |
|---|---|---|---|---|
| A | 2 | 1 | ||
| B | ||||
| C | 1 | 1 | ||
| D | 1 | 1 | ||
| E | 3 | 2 |
and I have a table of values for those items -
| name | value |
|---|---|
| A | 10 |
| B | 5 |
| C | 4 |
| D | 1 |
| E | 5 |
How can I sum the total value of items each player has using a single formula? I'm having trouble getting VLOOKUP, SUM, FILTER, etc to work well together.
Example output:
| name | total value |
|---|---|
| P1 | 39 |
| P2 | 1 |
| P3 | 20 |
| P4 | 5 |
CodePudding user response：
try:
=TRANSPOSE({{B1:1};BYCOL(B2:6,LAMBDA(bex,IF(COUNTA(bex)=0,,SUMPRODUCT(bex,{10;5;4;1;5}))))})
CodePudding user response：
use:
=INDEX(TRANSPOSE({B1:E1; BYCOL(IFNA(VLOOKUP(
IF(B2:E="",,A2:A), G2:H, 2, )*B2:E), LAMBDA(x, SUM(x)))}))
