Software

How to sum the top n values in Excel

There's usually more than one way to get a job done in Excel. Whether you prefer an expression or built-in filtering, summing the top n values is an easy task.

hero

Image: iStockphoto.com/AlexBrylov


Quickly discerning your top five customers or products isn't difficult in Excel. However, returning a quick total of your top five commissions is a bit harder. How you approach the solution depends on how you intend to use the resulting information. In this article, I'll show you two ways:

  • An expression that references an n input value, making it dynamic. This solution returns a single value you can easily reference in other expressions.
  • A filtering solution that displays a total and the values that compose the total

I'm using Excel 2016 desktop on a Windows 10 64-bit system, but both solutions are compatible back to Excel 2007. The expression solution is compatible with 365's browser version. The filtering solution can be used in the browser version, but not created there. You can work with your own data or download the .xlsx demonstration file.

Clarifying terms

It's important to clarify the terms top and large within the context of this article. Top refers to the entry's value, not its position within the data set. For instance, in the data set {0, 1, ... 9, 10}, 10 is the top or largest value regardless of where it occurs within the set. This article uses both terms to remain consistent with Excel: We'll use Excel's Top 10 filter and LARGE() function. For our purposes here, the terms top and large mean the same thing.

Use an expression

The simple data set in Figure A uses the following expression to sum the top n values in the Sold column:

=SUMIF(D4:D11,">="&LARGE(D4:D11,$C$1))

This expression depends heavily on the LARGE() function to return the nth largest number in the specified column. This function uses the following syntax:

LARGE(datarange, n)


where datarange identifies the evaluated values and n specifies the position from the largest value within that range. If n is greater than the number of values in datarange, the function returns 0. You can wrap the function in an IFERROR() function or use conditional formatting to alert you when this happens. (Note: IFERROR() isn't available in Excel 2003 or earlier.) If datarange is a fixed size, you could apply data validation to the input cell. (Use SMALL() to return the nth smallest number.)

Figure A

2016014a.jpg
The SUMIF() function sums only the Sold values that are greater than or equal to the value returned by LARGE().

In our example, n references the input value in C1, which is 1. Consequently, the LARGE() function returns only one value, 87. If you change n factor to 2, LARGE() returns 87 and 82, and SUMIF() returns 169.

If you add a row to datarange, you must update the expression in C2 accordingly. To avoid this limitation, you can convert the data set into a Table object (available in Excel 2007 and later) and update the expression to reference the Table. To convert the data set into a Table:

  1. Click anywhere inside the data range.
  2. Click the Insert tab.
  3. Choose Table from the Tables group.
  4. In the resulting dialog, check the My Table Has Headers option (Figure B).
  5. Update the range =$B$1:$D$11 to =$B$3:$D$11. Specifically, change the first 1 to 3. Rows 1 and 2 aren't part of the header, but Excel will include them because there isn't a blank row between rows 2 and 3.
  6. Click OK.

Figure B

2016014b.jpg
Specify that the data has a row of header cells.

Next, modify the expression in C2 to reference the Table as follows:

=SUMIF(Table2[Sold],">="&LARGE(Table2[Sold],$C$1))


Now the expression updates automatically when you add new records. To learn more about the SUMIF() function, read SUMIF function.

SEE: How to import Access web app data into an Excel workbook for reporting

Use a filter

After converting your data set to a Table, you might not need an expression at all. For quick viewing, you can combine the Table's Total Row option and a built-in filter as follows:

  1. Click inside the data set.
  2. Click the contextual Design tab.
  3. In the Table Style Options group, check Total Row (Figure C).

Figure C

2016014c.jpg
Add a totaling row to the Table.

Currently, the SUBTOTAL() function in the totaling row evaluates all the records because there's no filter in place. Apply a top n filter to the Sold column as follows:

  1. Click the Sold dropdown arrow.
  2. Choose Number Filters.
  3. From the resulting submenu, choose Top 10. (This filter also offers a Bottom setting.)
  4. The filter defaults to an n value of 10. Change 10 to 2 (Figure D) and click OK.

Figure D

2016014d.jpg
Change the filter's top n default setting.

As you can see in Figure E, the SUBTOTAL() function in the totaling row evaluates only the visible records. If you enter a top value that's greater than the number of records in the data set, SUBTOTAL() evaluates all the records. (A similar filter is available when working with a PivotTable. From the column's dropdown list, choose Value Filters.)

Figure E

2016014e.jpg
Filtering the records updates the Table's totals.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

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

Free Newsletters, In your Inbox