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(<range>,ROW($1:$<N>)))
where <range> is the range of cells (for example, $A$1:$A$10) and <N> 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.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.00 7.00
Keywords : kbusage
Version : 4.00 4.00a 5.00 7.00 | 4.00 5.00
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.