Sum Max n Values Group Wise in Google Sheets

Published on

In order to sum max n values group wise, I am using the Query and running count array formula in Google Sheets.

This formula will be useful in many ways in your real-life. Like;

  1. Sum the highest/max ‘n’ scores of each player.
  2. Sum the max/highest ‘n’ sales done by each salesperson.
  3. Extract and sum the highest marks scored in any three subjects by all students.

You can relate so many like examples.

To sum max n values group wise in Google Sheets, as I have said, the running count plays an important role.

How to Sum Max n Values Group Wise in Google Docs Sheets

For every example, sample data is a must. I am taking this one, the marks of two students in five subjects, for our example purpose.

demo data to learn how to Sum Max n Values Group Wise in Sheets

First I am going to provide you with a formula that works in a sorted dataset. For unsorted dataset, there is another formula but both are almost similar.

Sum Highest n Values Group Wise in a Sorted Range

The above sample data contains sorted information. As you can see the names are sorted in column A. But we want column B also sorted that in descending order.

So first sort the data properly. To do that select the array A2:C11. Then go to the menu Data > Sort range. Do the sorting as per the below setting.

manual sorting of data in google Docs Sheets

Here I am taking the number 3 as ‘n’.

The Query is the key function that we can use to sum the highest n values in each group. But Query alone can’t do that.

We need to make a virtual helper column that contains the running count.

ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))

This is that formula that generates the virtual helper column. We can use this to generate a four column array as below.

={A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))}

This is a four column dataset which contains the “Name” in the first column, “Marks out of 100” in the second column, “Subject” in the third column, and the “Helper” column as the fourth column.

Use the comparison operator <= to limit the number of rows to three in each group as below. It’s possible because of the running count in the fourth column.

=Query({A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3")

With the help of another Query we can sum max 3 values group wise.

Final Formula:

=Query(Query({A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")
Sum max n values in each group in a sorted range

Sum Max n Values Group Wise in an Un-sorted Range

Suppose my data is not sorted or it’s imported using the Importrange and frequently updating. So it won’t be a wise choice to manually sort the data each and every time.

I want a set and forget formula to sum max n values group wise in Google Sheets irrespective of the data order.

You can use the above final formula with the following changes in an unsorted array.

Use the below SORT formula to replace the array A2:C in the above formula.

sort(A2:C,1,true,2,false)

Then use the below SORT formula to replace the array A2:A inside the COUNTIFS (only the first two references).

sort(A2:A,1,true)

So the final formula to sum max 3 values group wise in an un-sorted array would be as follows.

=Query(Query({sort(A2:C,1,true,2,false),ARRAYFORMULA(if(len(A2:A),COUNTIFS(sort(A2:A,1,true),sort(A2:A,1,true),ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")
Sum Max n Values Group Wise – Un-sorted Range
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.