id="info"

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.

Editor's Picks