1

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
  • share a copy of your sheet – user0 Jul 20 at 20:41
1

=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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.