My table looks like this
| Team | Language | People |
|---|---|---|
| Team A | English | 3 |
| Team B | English | 4 |
| Team B | Spanish | 3 |
| Team Spanish | Spanish | 4 |
| Team C | Portuguese | 4 |
If you notice, Team B handles English and Spanish and there's also a Team Spanish.
I want to combine Team Spanish with Team B - Spanish, so it only shows one row with the sum of 7 people.
My expected result:
| Team | Language | People |
|---|---|---|
| Team Spanish | Spanish | 7 |
My workaround is to create an extra column, combine the Team column with the Language column and then sum the results
SUMIF(people, aux_col IN ('team B - Spanish', 'Team Spanish - Spanish')) AS new_people_count
Never mind the last code, it's just pseudocode
CodePudding user response：
i made my calculation using google sheet.
I think you must complete your goal in two steps:
First step
Create a table like your original table.
This is the original table in google sheet
CodePudding user response：
I was overcomplicating my idea... This code solves the issue
CASE
WHEN CONCAT(team, '-', language) = 'Team B-Spanish' THEN 'Team Spanish'
ELSE team
END AS team,