How to count the unique pairs in 2 columns and sort using the count using an ArrayFormula in Google Sheets?

Question

Let's say I have the following spreadsheet:

https://docs.google.com/spreadsheets/d/1FY7GnhZoT2_Tzm8FLOkDuc5XR8TkFhBJKgW_qZ1r4Cc/edit?usp=sharing

On the top left column, I have a formula that counts the events and sorts them according to the frequency. Anyway, what I want to do now is instead of just counting the frequencies of the actions, I want to count the number of unique actions. For example, in my spreadsheet, the action call came up 5 times: 2 times by Joe, 2 times by Mary, 1 times without a user (empty). Therefore, next to the call action on my left-hand table, I would want 2 because the number of unique pairs (event and user) is exactly 2.

So using the above logic, what I want my left side table to be is the following:

Call   2
SMS    1
Review 1

Hopefully, I have made myself clear.

How can I do this using my example spreadsheet? Thanks.

player0 player0 · Accepted Answer · 2020-01-20T23:57:40

try:

=ARRAYFORMULA(QUERY(UNIQUE({D:D, D:D&E:E, E:E}),
 "select Col1,count(Col1)
  where Col3 != '' 
  group by Col1 
  order by count(Col1) desc 
  label count(Col1)'Count'", 1))

How to count the unique pairs in 2 columns and sort using the count using an ArrayFormula in Google Sheets?

1 Answers