Google-sheets – Summarize least relevant entries of table into a “Others” row

google sheets

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?

Best Answer

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

Related Topic