Excel: How to sum largest numbers in a range

Six efficient methods for summing the largest N values in Excel. Adding numbers is a common task, and while Excel offers SUM, SUMIF, and SUMIFS, directly summing the top N values requires a different approach.

Method 1: SUM and LARGE for Top N Numbers

The formula SUM(LARGE(range, {1,2,3,...,n})) sums the largest N numbers. For example, =SUM(LARGE(B2:B15, {1,2})) sums the two largest numbers in B2:B15. If ranks are in cells (e.g., D2:D4), use =SUM(LARGE(B2:B15, D2:D4)) (enter as an array formula with Ctrl Shift Enter in older Excel versions). Note: Ties are handled by summing only the first occurrence.

Method 2: SUMPRODUCT and LARGE for Flexibility

SUMPRODUCT(LARGE(range, {1,2,3,...,n})) offers similar functionality to Method 1 but works equally well with array constants or cell references in all Excel versions. For instance, =SUMPRODUCT(LARGE(B2:B15, D2:D4)) sums the largest numbers based on ranks in D2:D4.

Method 3: SUMPRODUCT, LARGE, ROW, and INDIRECT for Many Top Numbers

For summing many top numbers (e.g., top 10 or 20), avoid manually listing array constants. Use SUMPRODUCT(LARGE(range, ROW(INDIRECT("1:n")))) or SUM(LARGE(range, ROW(INDIRECT("1:n")))) (array formula in older Excel). Replace "n" with the desired number. For dynamic input (e.g., cell E2), use =SUM(LARGE(B2:B15, ROW(INDIRECT("1:"&E2)))) or the SUMPRODUCT equivalent. In Excel 365 and 2021, these work as regular formulas.

Method 4: Handling Insufficient Data

If n exceeds the data count, use =SUMPRODUCT(LARGE(range, ROW(INDIRECT("1:"&MIN(n, COUNT(range)))))) to sum all available values or =IFERROR(SUMPRODUCT(LARGE(B2:B15, ROW(INDIRECT("1:"&E2)))), "Insufficient data") to display a message.

Method 5: Excel 365 and 2021: SEQUENCE Function

Excel 365 and 2021 offer a simpler solution: SUM(LARGE(range, SEQUENCE(n))). For example, =SUM(LARGE(B2:B15, SEQUENCE(E2))) sums the top n numbers (where n is in E2).

Method 6: Using Excel Tables

Convert your range to a table (Ctrl T), enable the Total Row, use the filter to select "Top 10" (or your desired number), and Excel will automatically calculate the sum.

These methods provide flexible and efficient ways to sum the largest N values in Excel, catering to different Excel versions and data scenarios. A practice workbook is available for download.

The above is the detailed content of Excel: How to sum largest numbers in a range. For more information, please follow other related articles on the PHP Chinese website!