Microsoft

Sum the top n values in a range using dynamic table behavior in Excel

Summing values is simple, summing the n top values in a range complicates the task, but it's certainly not impossible for Excel.

Counting the top n values is a common task, summing them complicates things a bit, but it's easier than you might think. You might consider sorting the values and referencing the appropriate cells, but doing so in inefficient.

If n varies and you enter and delete data, you need a dynamic formula in the form:

=SUMIF(range,">="&LARGE(range,n))

where range identifies the data you're summing (in a table) and n is the value that represents the number of values you want to sum. The sheet below shows a column of values that aren't in any particular order, except perhaps, data entry order. Using Excel's new table feature, I named the table in A4:C11 topn. The formula will work without the table, but you'll have to update the column references when you add data . Do yourself a favor and use a table.

In the sheet above, B1 is n in the following formula:

=SUMIF(topn[Sold],">="&LARGE(topn[Sold],B1))

The LARGE() function returns the nth largest number in the specified range. The SUMIF() function then sums all values in the range that are greater than or equal to the result of LARGE(). For example, in the above sheet, LARGE() returns 33. Therefore, SUMIF() sums the following values: 87, 73, 75, 33, and 82. The result is 350. 

Changing the value in B1 changes the n factor and the formula updates accordingly. In addition, if you enter 0, the formula returns 0, not an error. If you enter a number that's greater than the actual number of values in the column, the formula returns 0. You'll know something's wrong, but you could use ISERROR() to return a more meaningful message.

I told you it was easy! Using Excel's dynamic table behavior and this simple nesting formula, you can quickly return the n top records. To sum the n lowest values, replace LARGE() with SMALL().

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
Kalumbal
Kalumbal

Thanks Susan My other issue was how to sum all the postive numbers in a column consisting of negative and positive numbers. The problem was not knowing n or the number of positive numbers. So I replaced n with the countif formula as below 6 2 6 7 5 -5 1 -1 1 2 0 -6 -8 -2 -7 6 36 =SUMIF(A1:A16,">="&LARGE(A1:A16,COUNTIF(A1:A16,">0")))

lee_dimambro
lee_dimambro

To add only the unique values for the 4th largest. If the count in the Data column is not 1 then return 0 else return the value in A The 4th largest number excluding duplicates is 3 A B C Data Unique values Sum of 4th largest 6 6 18 5 5 5 0 5 0 4 4 4 0 3 3 3 0 2 2 1 1 The formula in B is '=IF(COUNTIF($A$2:A2,A2)=1,A2,0) The formula in C is =SUMIF($B$2:$B$11,">=" &LARGE($B$2:$B$11,4))

david.hanshumaker
david.hanshumaker

Great example of creativity in using nested worksheet functions to accomplish a task with a single expression.

Lost Cause?
Lost Cause?

Shouldn't this have read "I named the table in A4:C11 topn"?

AC2
AC2

If there are duplicate values in the range that meet the criteria, more than n could be summed. In this example if there were a tie for 5th place, both (or more) would be summed.

ulhas_gore
ulhas_gore

I think the range named (topn) is A4:B11& no need of [Sold] in the formula.Otherwise its a wonderful alternative to doing the same thing using array Ulhas Gore

DBlayney
DBlayney

The issue becomes a semantic question as to whether "sum the top 5 values" should actually include a repeated fifth-ranked value. Assuming that this is not the case then a tie for fifth only matters if you then want to list the personnel who contributed the top 5 Sold values - this list would then have to contain more than 5 names and their total sales would not be equal to the sum. Notwithstanding the above, it's still a neat idea. By the way: Susan wrote "Counting the top n values is a common task". Would I be wrong to suggest that the answer is always "n"? (Except where there is a tie as above)

DBlayney
DBlayney

Removing [Sold] from the formula has no visible effect in this case because the values in the other column are all non-numeric and are treated as zeros. If there were, for example, another column of numeric values in the table then the LARGE function would include them in the ranking and the result would probably be wrong.

Editor's Picks