Google Sheets Query and Aggregate Date Scattered Across Multiple Columns

Question

I am working on this Google Sheet: https://docs.google.com/spreadsheets/d/1Ai8P8T8GmLMzTxgcxQFhHTqbUK6hG4TrJBtvP9PWC9M/edit#gid=0

I have the following formula in G1 (red shading):

=QUERY(A:E,"select C, count(C)+count(D)+count(E) GROUP BY C ORDER BY C ASC")

The results it creates only select from the unique names in column C, but I want it to select from the unique names across C, D and E. I also want to flatten the output into a single column of names. The difficulty with my data is that unique results are scattered across C, D and E. The results are also faulty because it seems to double the count for "Joe".

The actual results I want it to create should look like the formulas used in L1:M5 (green shading).

Is there a simple way to do this with a QUERY? I know it's simpler to use a formula like in L1:M5 but I want to be able to construct a more complex selection/where using the QUERY function.

Thanks in advance!

Answer 1

=query(flatten(C2:E),"select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1) ''",0)

FLATTEN all the names into one column first, then run the QUERY on that column.