How to sum largest or smallest 3 values in a list of Excel?

In Excel, calculating the sum of an entire range of numbers is straightforward using the SUM function. However, business and data analysis scenarios often require summing just the largest or smallest few numbers within a dataset—such as identifying the top 3 sales, or assessing the lowest 5 expenses. Performing this task manually can be tedious and error-prone, especially as your dataset grows. Fortunately, Excel offers a variety of approaches to effectively solve this problem.

Below, you'll find comprehensive step-by-step methods including formula solutions, a practical tool-based alternative, a VBA macro for more advanced automation, and a Pivot Table method suitable for grouped data. Each approach suits different circumstances, letting you choose the best fit for your needs.

Sum the largest / smallest 3 values in a range with formulas

Sum the largest or smallest n values in a range with VBA code

Sum top n values per category using Pivot Table

Sum absolute values in a list with Kutools for Excel

---

Sum the largest / smallest 3 values in a range with formulas

Suppose you have a dataset arranged in Excel and wish to quickly sum the top three or bottom three values. This is commonly needed in performance reviews, ranking analyses, or when focusing on outlier data points.

There are two commonly used formula methods for this solution:

1. LARGE and SMALL function formulas:

Use the LARGE function to identify the nth largest values in your selected range, and then sum them. This method is direct and easily adjustable if you need to sum a different number of top or bottom values.

Type the following formula in a blank cell (for example, E1):

=LARGE(A1:D10,1)+LARGE(A1:D10,2)+LARGE(A1:D10,3)

After entering the formula, press Enter to see the sum of the largest 3 values. 

Once done, the result will appear in your selected cell.

Notes:

• To sum the top 5 values, expand the formula accordingly:

  =LARGE(A1:D10,1)+LARGE(A1:D10,2)+LARGE(A1:D10,3)+LARGE(A1:D10,4)+LARGE(A1:D10,5)

• For summing the smallest values, use the SMALL function in a similar fashion:

  =SMALL(A1:D10,1)+SMALL(A1:D10,2)+SMALL(A1:D10,3)

This approach works well for small n values, but for large n, manual expansion becomes cumbersome.

2. Array formulas for larger / small n values:

For larger sums, use array formulas for better scalability and cleaner syntax:

Type in a cell:

=SUM(LARGE(A1:D10,{1,2,3}))

Then, press Ctrl + Shift + Enter, not just Enter, if using older Excel versions. This calculates the sum of the largest three values. Adjust the curly bracketed numbers according to the quantity needed.

If you need to sum the largest 20 values, try:

=SUM(LARGE(A1:D10,ROW(INDIRECT("1:20"))))

Again, finalize using Ctrl + Shift + Enter. For Excel 365 and later, standard Enter suffices due to improved array formula support. Change "20" to any n you need.

Similarly, to sum the smallest n values, use:

=SUM(SMALL(A1:D10,{1,2,3}))

=SUM(SMALL(A1:D10,ROW(INDIRECT("1:3"))))

---

Sum the largest or smallest n values in a range with VBA code

For users who need to frequently calculate the sum of the top or bottom n values, or wish to automate the process for different datasets, a VBA macro offers an efficient solution. This is especially helpful if the value of n changes regularly or data ranges are large.

This macro can sum either the largest or smallest n values from your selected range, according to your preference. It simplifies repetitive analysis and is easy to update or customize.

1. Click Developer Tools > Visual Basic to open the VBA editor window. (If you don't see Developer Tools, enable it through Excel Options > Customize Ribbon.)
2. In the VBA editor, select Insert > Module. Paste the following code into the new module:

Sub SumTopOrBottomNValues()
    Dim WorkRng As Range
    Dim n As Integer
    Dim i As Integer
    Dim arr() As Double
    Dim result As Double
    Dim choice As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select a range:", xTitleId, WorkRng.Address, Type:=8)
    
    choice = Application.InputBox("Type 'L' for largest, 'S' for smallest:", xTitleId, "L", Type:=2)
    
    n = Application.InputBox("Sum how many values? n =", xTitleId, 3, Type:=1)
    
    If WorkRng Is Nothing Then Exit Sub
    
    ReDim arr(1 To WorkRng.Count)
    i = 1
    
    For Each cell In WorkRng
        If IsNumeric(cell.Value) Then
            arr(i) = cell.Value
        Else
            arr(i) = 0
        End If
        i = i + 1
    Next
    
    If choice = "L" Or choice = "l" Then
        Call BubbleSortDescending(arr)
    ElseIf choice = "S" Or choice = "s" Then
        Call BubbleSortAscending(arr)
    Else
        MsgBox "Invalid choice. Enter 'L' or 'S'."
        Exit Sub
    End If
    
    result = 0
    For i = 1 To WorksheetFunction.Min(n, UBound(arr))
        result = result + arr(i)
    Next
    
    MsgBox "Sum of " & n & " " & IIf(choice = "L" Or choice = "l", "largest", "smallest") & " values is: " & result
End Sub

Sub BubbleSortDescending(arr() As Double)
    Dim i As Integer, j As Integer, temp As Double
    
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) < arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

Sub BubbleSortAscending(arr() As Double)
    Dim i As Integer, j As Integer, temp As Double
    
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

3. Once you have pasted the code, click the (Run) button to execute the macro. You will be prompted to:

• Select your data range;
• Enter 'L' to sum largest values, or 'S' for smallest ones.
• Input the desired quantity n.

After confirmation, the macro calculates and displays the sum for your specified criteria. If your range includes non-numeric values, these are treated as zero to avoid calculation errors. For robust accuracy, always ensure your selection is correct and you enter a valid number for n.

---

Sum top n values per category using Pivot Table

If your data is grouped by categories (such as sales regions, product lines, etc.), and you need to sum the top n values per group, Pivot Tables provide a powerful solution using their built-in value filters.

This method is best applied to tabular data with clear category and value columns. For example, you may wish to sum the top3 sales figures in each region.

1. First, select your data table and insert a Pivot Table from the Insert tab.
2. In the Pivot Table field list, drag your Category field to the Rows area and your numeric Value field to the Values area (ensure it is set to SUM).
3. Next, click the dropdown arrow next to the Value field in the Row Labels area, then choose Value Filters > Top 10.
4. In the dialog, set 'Top' to 3 (or any n you want) and specify the field to filter. This limits the Pivot Table to show only the top n items per category.
5. The table will then display the sum of your filtered values for each group.

Note: Pivot Table value filters work best if your data is well-formatted and categories are consistent. If you need to sum bottom n values, reverse the filter via "Bottom n" instead of "Top n". This method is flexible for summary reports and quick visual analytics. Be aware that Pivot Tables update dynamically with data changes, but may require manual refresh (right-click table > Refresh).

---

Sum absolute values in a list with Kutools for Excel

In certain cases, you may have a list containing both positive and negative numbers and want to sum all their absolute values rather than their arithmetic sum. This is typically useful in financial and scientific calculations where magnitude matters. Kutools for Excel offers a convenient “Sum absolute values” function to complete this task efficiently.

After installing Kutools for Excel, proceed as follows:

1. Select any blank cell for the result. Then click Kutools > Formula Helper > Formula Helper .

2. In the Formula Helper window, check the Filter box and type "sum" in the search bar. Choose Sum absolute values from the displayed formulas. Then in the Argument input area, click to specify your range, and confirm with Ok.

Right away, the sum of the absolute values will be calculated and displayed.

Kutools streamlines this process, reducing the risk of formula errors and saving time, especially for those not familiar with complex Excel functions. Note, however, that this feature focuses on absolute value summations—which is tangential to the topic of summing the top or bottom values specifically, but often invaluable in accounting, reconciliation, or measurement-related scenarios.

---

Related articles:

• Sum or count odd/even numbers only in Excel
• Sum or count the cells greater than but less than a number
• Sum time and format as hours, minutes or seconds in Excel
• Sum each column data in Excel