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!

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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