Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

In Google Sheets, how to list and sum values in some rows but not others depending on string?

I am trying to join gained level for names in a list. Some names are the same person who has changed handle and the scores should be summed.

I have created a sheet to track levels for players in rankings over time. My implementation is not as clever as I would like it to be. Also, there is a problem with some players changing names.

Example Sheet

Currently, I have 

=SORT(UNIQUE({}))

In order to produce just one of each name in a list In the cell next to that I'm using

=IFERROR(INDEX(MATCH())) + IFERROR(INDEX(MATCH())) + IFERROR(INDEX(MATCH()))

to the sum of levels for each name across several ranges /sessions.

In the example sheet, N7 and N10 is the same person but my SORT, INDEX, as well as QUERY cannot handle this. I would like to (manually type in the names as strings that belong to the same person) and that the latest handle is the one used in query output together with the sum of all gains.

Any direction pointers or suggestions as to how I could improve my current implementation or even solve the problem I'm having would be appreciated.

Solution 
  • =ARRAYFORMULA(QUERY({Ranking!CF4:CF200\ SUBSTITUTE(Ranking!CG4:CG200; "N7"; "N10")}; 
 "select Col2,sum(Col1)
  where Col2 is not null
  group by Col2
  label sum(Col1)'',Col2'Total levels gained since 15 April 2018'"; 0))

    0