So lets say I have sheet1

a | 4
b | 5
c | 33
d | 55

and sheet2

a | 1
b | 2
c | 3
e | 2

And using this query

=query({Sheet1!A1:B10;Sheet2!A1:B10},"select Col1, Avg(Col2), count(Col1) where Col1 is not NULL group by Col1 label Col1 'name', count(Col1) 'number of times referenced?', Avg(Col2) 'average'")

I get sheet 3 below

name | average | number of times referenced?
a         2.5              2
b         3.5              2 
c         18               2  
d         55               1
e         2                1

The problem is it is automatically sorted by the name column, but I would like to be able to sort by all 3 columns if possible.

an example if I decided to sort by average it would look like

name | average | number of times referenced?
d        55              2
c        18              2 
b        3.5             2  
a        2.5             1
e        2               1
  • I mean being able to sort by each of the 3 columns one at a time. So I could sort by average on its own, name on its own and number of times referenced on its own. But at the moment when I try to sort Average or number of times column1 just auto sorts again – John Sumner Nov 15 '17 at 15:27
  • 1
    I've updated the example showing what I mean by sorting by average – John Sumner Nov 15 '17 at 15:33

Either adapt the QUERY to sort according to the desired column (and then again for a different choice), for example to sort on average descending insert

 order by Avg(Col2) desc

before label, or:

Select/Copy/Paste special/Paste values only and then apply the standard sheet sorting.

| improve this answer | |
  • I guess that's what I'll have to do – John Sumner Nov 15 '17 at 15:34

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.