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.

Thanks!

*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"))

Answers


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

Search




Leave an answer