In Google Sheets I have table like the following
| Home Player 1 | Home Player 2 | Away Player 1 | Away Player 2 | Home Goals | Away Goals |
|---|---|---|---|---|---|
| Ronaldo | Messi | Neymar | Aguero | 2 | 1 |
| Aguero | Ronaldo | Neymar | Messi | 1 | 1 |
| Messi | Aguero | Ronaldo | Neymar | 0 | 2 |
I need to aggregate the Players columns into one column and show how many goals each player's team has scored.
The final table would look like this:
| Player | Goals |
|---|---|
| Ronaldo | 5 |
| Messi | 3 |
| Neymar | 4 |
| Aguero | 2 |
What formula can I use to achieve this?
CodePudding user response：
You could try
=query({A2:A,E2:E; B2:B,E2:E; C2:C,F2:F; D2:D,F2:F}, "Select Col1, sum(Col2) where Col1 <>'' group by Col1 order by sum(Col2) desc label Col1 'Player', sum(Col2) 'Goals'", 0)
