How to GROUP and ORDER data in a Google Spreadsheets?

Question

I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions.

Can I order by sum?

This is an example file I created: order by sum

Something in my web site or project doesn't work. Can I just paste a link to it? – please improve your question. — fuxia, Commented Aug 30, 2013 at 2:59

pnuts · Accepted Answer · 2017-12-15 22:45:21Z

8

Another way is from within the query:

=QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A order by sum(B) desc")

answered Dec 15, 2017 at 22:45

pnuts

17.8k5 gold badges54 silver badges103 bronze badges

Add a comment |

Jacob Jan · Accepted Answer · 2013-08-30 05:33:10Z

7

The best result would be to use the QUERYfunction:

=SORT(QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A");2;FALSE)

Since the sum already has been sorted ascendingly, the FALSE statement in the formula will sort descending using column 2.

answered Aug 30, 2013 at 5:33

Jacob Jan

23.3k16 gold badges106 silver badges195 bronze badges

If the query includes a header row, the header gets sorted as well. How would this be used with a header row?
– hughes
Commented Sep 7, 2019 at 20:29
1

@hughes The third optional parameter of the QUERY function allows you to indicate a header. If omitted, Google will do that for you and leave the headers un-touched.
– Jacob Jan
Commented Sep 9, 2019 at 6:44
1

This was super helpful. I was managed to produce nice graphs with very little effort from Google Forms data with this and the answer here webapps.stackexchange.com/a/146778/29110
– Mikko Ohtamaa
Commented Oct 12, 2020 at 14:29

Add a comment |

2 Answers 2