Google Sheets Query – Sum for Unique Pairs of Rows

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.

City          Person    Days
London        Tom         6
London        Peter       2
Brussels      Josef       1
London        Peter       8
Prague        Ryan        5
Oslo          Peter       4

I want to count the total number of days an employee spends in a city. The order should stay the same. So for every unique city-employee pair, all further occurrences get deleted and just the days' count gets summed up. The code should work as well if there are additional columns.

City          Person    Days
London        Tom         6
London        Peter       10
Brussels      Josef       1
Prague        Ryan        5
Oslo          Peter       4

Best Answer

=QUERY(A1:C, "select A,B,sum(C) 
              where C is not null 
              group by A,B
              label sum(C)'Days'", 1)

0

=ARRAYFORMULA(IFERROR((VLOOKUP(UNIQUE(A1:A&B1:B), QUERY({A1:A&B1:B, A1:C}, 
 "select Col1,Col2,Col3,sum(Col4) 
  where Col4 is not null 
  group by Col1,Col2,Col3
  label sum(Col4)'Days'", 1), {2,3,4}, 0))))

0

Related Topic