Home Ask Login Register

Developers Planet

Your answer is one click away!

Jason February 2016

Google Spreadsheet QUERY()

I use the following query to get the Top 5 items from a table in Google Spreadsheets. I would like to revise my query to get the Top 5 items AND the sum of the rest of the items in the table not part of the Top 5. eg: 10 rows total, Top 5 are separate rows and sum of 6-10 are one separate row named Other Items. I know how to do it using tSQL but it does not work in the subset of Google Query. Any help from someone familiar with the Google subset would be appreciated.


*Also I would be willing to have 2 separate queries but ideally it should be 1 single query.

=ARRAYFORMULA(QUERY($A$2:$G$24, "select A, G order by G desc limit 5"))


Chris Hick February 2016

This formula might work as you want:

={QUERY($A$2:$G$24, "select A, G order by G desc limit 5");"Other Items",SUM(QUERY($A$2:$G$24, "select G order by G limit "&COUNTA($A2:$A24)-5))}

Post Status

Asked in February 2016
Viewed 3,961 times
Voted 10
Answered 1 times


Leave an answer

Quote of the day: live life