How to count totals and uniques (based on 2 separate columns) using ArrayFormula in Google Sheets?

Question

I have the following spreadsheet:

https://docs.google.com/spreadsheets/d/1pBL21fyuxYNb5YfERsFl7Y-CTdQEH7SgJQfuOdQ0V6c/edit?usp=sharing

The first 2 columns count the total number of actions and sort them. What I want to do is also generate the next 2 columns using the same formula. The 3rd column is meant to count the unique number of actions based on user id, and the 4th column is meant to count the unique number of actions based on the user name. I can do a formula that counts the uniques separately, but ideally, I would like it to work together with the first 2 columns.

Any help is greatly appreciated.

Answer 1

Try the following formula in cell A10 or any free cell except in columns F,G and H:

=arrayformula({query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 label count(Col2) 'Total'",1),iferror(vlookup(query(query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 label count(Col2) 'Total'",1),"select Col1"),query(query({F3:G,G3:G},"select Col1,Col2,count(Col3) where Col1<>'' group by Col1,Col2 label count(Col3) 'Total'",1),"select Col1,count(Col2) group by Col1 label count(Col2) 'Unique (for id)'",1),2,0)),iferror(vlookup(query(query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 label count(Col2) 'Total'",1),"select Col1"),query(query({F3:F,H3:H,H3:H},"select Col1,Col2,count(Col3) where Col1<>'' group by Col1,Col2 label count(Col3) 'Total'",1),"select Col1,count(Col2) group by Col1 label count(Col2) 'Unique (for name)'",1),2,0))})

To get sorted (by column 2) result, try:

=arrayformula({query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 order by count(Col2) desc label count(Col2) 'Total'",1),iferror(vlookup(query(query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 order by count(Col2) desc label count(Col2) 'Total'",1),"select Col1"),query(query({F3:G,G3:G},"select Col1,Col2,count(Col3) where Col1<>'' group by Col1,Col2 label count(Col3) 'Total'",1),"select Col1,count(Col2) group by Col1 label count(Col2) 'Unique (for id)'",1),2,0)),iferror(vlookup(query(query({F3:F,F3:F},"select Col1,count(Col2) where Col1<>'' group by Col1 order by count(Col2) desc label count(Col2) 'Total'",1),"select Col1"),query(query({F3:F,H3:H,H3:H},"select Col1,Col2,count(Col3) where Col1<>'' group by Col1,Col2 label count(Col3) 'Total'",1),"select Col1,count(Col2) group by Col1 label count(Col2) 'Unique (for name)'",1),2,0))})