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.

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.

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