I'm working with a table of data as following:

User ID:       Event name:
111            Event 1
222            Event 2
333            Event 3
111            Event 4
444            Event 5
111            Event 6

So, what I'm trying to achieve is get a report in another sheet, which would

a) correspond user ID: 111, 222, 333, 444 with user names: name 1, name 2, etc.
b) count the total number of events per each user. Any idea how this can be resolved?

  • create a sheet where you assign IDs to names
  • use this simple query formula:

    =ARRAYFORMULA(QUERY({IFERROR(VLOOKUP(Sheet1!A1:A, Sheet2!A1:B, 2, 0)), Sheet1!B1:B}, 
     "select Col1, count(Col1) 
      where Col2 contains 'event' 
      group by Col1 
      label Col1'Name', count(Col1)'Events count'"))


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.