Summing the Largest N Values by Group in Google Sheets

Published on

You can easily sum the largest N values by group in Google Sheets using a combination of SORT, COUNTIFS and QUERY. This technique is useful in various fields such as education, sales, sports, and more.

For example, you can use this method to:

  • Consider only the top N subject scores for student admissions.
  • Calculate the best N months’ total sales for a salesperson.
  • Sum the fastest N race times for qualification rankings.
  • And many other use cases!

Generic Formula

=LET(
   category, A2:A, score, B2:B, n, 3, 
   rangeS, SORT(HSTACK(category, score), 1, TRUE, 2, FALSE), 
   rcS, ARRAYFORMULA(COUNTIFS(SORT(category, 1, TRUE, score, FALSE), SORT(category, 1, TRUE, score, FALSE), ROW(category), "<="&ROW(category))), 
   ftr, FILTER(rangeS, rcS<=n), 
   QUERY(ftr,"SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2)''")
)

How to Use This Formula?

  1. Replace A2:A with the category column reference (e.g., Student Names).
  2. Replace B2:B with the score column reference (e.g., Marks or Sales).
  3. Replace 3 with your desired value of N (e.g., sum the largest 3 values per group).

This formula will instantly sum the largest N values by category, generating a summary in one step!

Example: Summing the Largest N Values by Group in Google Sheets

Consider the following dataset, which contains student names, subjects, and marks:

Sample dataset for summing the largest N values by group

Goal:

We want to sum the highest 3 subject scores for each student.

Formula for This Example:

=LET(
   category, A2:A, score, C2:C, n, 3, 
   rangeS, SORT(HSTACK(category, score), 1, TRUE, 2, FALSE), 
   rcS, ARRAYFORMULA(COUNTIFS(SORT(category, 1, TRUE, score, FALSE), SORT(category, 1, TRUE, score, FALSE), ROW(category), "<="&ROW(category))), 
   ftr, FILTER(rangeS, rcS<=n), 
   QUERY(ftr,"SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2)''")
)

Explanation:

  • A2:A represents the category (student names).
  • C2:C represents the score (marks).
  • 3 means we are summing the top 3 scores for each student.

Output:

Student 1277
Student 2276

Formula Logic & Breakdown

1. Sorting the Data

SORT(HSTACK(category, score), 1, TRUE, 2, FALSE)
  • Sorts data by category (A-Z).
  • Sorts scores in descending order (so the highest scores appear first).
NameMarks
Student 198
Student 190
Student 189
Student 187
Student 186
Student 296
Student 290
Student 290
Student 278
Student 275

2. Assigning a Running Count

ARRAYFORMULA(COUNTIFS(SORT(category, 1, TRUE, score, FALSE), 
SORT(category, 1, TRUE, score, FALSE), ROW(category), "<="&ROW(category)))
  • Assigns a rank to each score within its category.
  • Ensures that only the top N values are selected for summation.

3. Filtering the Top N Scores

FILTER(rangeS, rcS<=n)
  • Keeps only the highest N values per group.
NameMarks
Student 198
Student 190
Student 189
Student 296
Student 290
Student 290

4. Summing the Largest N Values by Group

QUERY(ftr, "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2)''")
  • Groups data by category (student name).
  • Computes the sum of the highest N scores per category.

Why Use This Method?

This approach works seamlessly with both sorted and unsorted data, making it highly flexible.

It can handle any number of groups or categories, allowing you to sum the largest N values by category dynamically.

Best of all, it eliminates the need for manual filtering or sorting, saving time and effort while ensuring accuracy.

🔹 How to Find the Highest N Values in Each Group in Google Sheets
🔹 How to Filter Top 10 Items in a Google Sheets Pivot Table

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.