Microsoft KB Archive/119239

= Microsoft Knowledge Base =

Excel: How to Sum the N Largest Items in a Range
Last reviewed: March 27, 1997

Article ID: Q119239

4.00 4.00a 5.00 7.00 | 4.00 5.00

WINDOWS             | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0
 * Microsoft Excel for Windows 95, version 7.0

SUMMARY
In Microsoft Excel, you can create a formula to sum the N largest items in a range of cells, where N is the number of largest items to sum.

MORE INFORMATION
In Microsoft Excel, you can sum the largest numbers in a range of cells with the following formula

=SUM(LARGE( ,ROW($1:$))) where is the range of cells (for example, $A$1:$A$10) and  is the number of items to sum.

NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

For example, assume you have the following numbers in a worksheet:

A1: 1  B1: 2   C1: 3   D1: 4   E1: 5   F1: 6   G1: 7   H1: 8

To sum the five largest numbers you can use the following formula:

=SUM(LARGE($A$1:$H$1,ROW($1:$5))) When entered as an array, this formula will return the value 30.

This formula even works correctly if there are duplicate items within the range. For example, assume you have the following numbers:

A1: 1  B1: 4   C1: 4   D1: 4   E1: 5   F1: 6   G1: 7   H1: 8 Even though there are three 4s in the range, the formula only uses one of them because the numbers in the range are "ranked" like this:

Number  Rank --

8       first 7       second 6       third 5       fourth 4       fifth 4       sixth 4       seventh 1       eighth Even though the three 4's share the same value, only one of them is counted in the five largest numbers, so the formula returns the correct value, 30.