0

I have a table with ~100 rows that can increase or decrease over time. I want to filter this table so that it shows the top N items and the sum of the rest in synthetic row called "Others". I don't want to use Google Apps Script for this.

How should I approach this in Google Sheets?

1

You have a sheet with many rows containing values; you want to sum the top5 values, and show the total for the balance of the values.

It is likely that there are many ways that this question can be answered. Please consider the following as one example.

Use this formula:

={
  QUERY(
     $A$2:$B,
     "select A, B order by B desc limit 5 LABEL A 'Name',B 'Value'",
     1
   )
 ;
 {
  "Total Top 5",
  sum(query(B2:B,"select B order by B desc limit 5 LABEL B ''"))
 }
;
{
  "",
  ""
}
;
{
  "All others",
  query({QUERY(A2:B13,"select  B order by B asc limit  "&(COUNT(B2:B)-5)&"LABEL B ''")},"select sum(Col1) LABEL sum(Col1) ''")
}
;
{
  "Grand Total",
  sum(B2:B)
}
}

Logic

  • the top 5 items:

    • QUERY($A$2:$B,"select A, B order by B desc limit 5 LABEL A 'Name',B 'Value'",1)
    • a simple query ordering the data by value (Column B) descending, and limiting output to five.
  • sum of the top% items

    • sum(query(B2:B,"select B order by B desc limit 5 LABEL B ''"))
    • simply return the values for Column B and wrap the result in SUM
  • Total of the others:

    • query({QUERY(A2:B13,"select B order by B asc limit "&(COUNT(B2:B)-5)&"LABEL B ''")},"select sum(Col1) LABEL sum(Col1) ''")
    • get the values for column B, order by ascending, limit to count of the rows minus 5, and Sum(B) in the query.
  • Grand Total

    • sum(B2:B)
    • sum column B of the source data.

Sample data

sample data


Results

Results

| improve this answer | |
  • Thank you! This is fantastic! – Oren Pinsky Nov 17 at 11:51

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.