Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and SMALL functions. SMALL can return smallest values based on criteria, SUMPRODUCT can sum up these smallest values.

Through a simple instance, we will introduce you the syntax, argument and the usage of SUMPRODUCT and SMALL functions. We will let you know how the formula works step by step clearly. After reading the article, you may know that in which situations we can choose SUMPRODUCT function to sum data.

1. Sum the Smallest N Values Using SUM Formula

In the ‘Value’ column, there are 10 values from ‘1’ to ‘10’ in improper order. Now we want to sum the smallest 4 values from this list, how can we get the correct result?

Actually, we can sort these numbers from smallest to largest firstly by ‘Sort A-Z’ in excel.

Step1: Click ‘Data’ tab, under ‘Sort & Filter’ section click ‘Sort Smallest to Largest’ icon.

Step2: Then you can find numbers are ordered properly.

Step3: Then you can enter SUM function in B2 and select the first four numbers ->cell reference A2:A5 in the list.

Step4: Then you can get the sum of the smallest four values.

2. Sum the Smallest N Values Using SUMPRODUCT Formula

We can also get sum conveniently and correctly by just enter a formula. As we want to sum the smallest four numbers from range A2:A11, we can find the smallest N number or numbers by SMALL function actually, and then use SUMPRODUCT function to sum array directly.

Step1: In B2, enter the formula

=SUMPRODUCT(SMALL(A2:A11,{1,2,3,4}))

Step2: Press Enter after typing the formula.

We can see that this time we also get correct result 10. The formula works correctly.

HOW THIS FORMULA WORKS

The formula is converted as:

=SUMPRODUCT(SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}))

Now we will show you how the formula works with the two functions.

For SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}), refer to k value {1,2,3,4}, we find out the smallest 4 values in the array {1;3;5;6;9;10;7;8;4;2}.

Select SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}) in the formula bar, press F9. We can get the result {1,2,3,4}.

Obviously, the final result is 1+2+3+4=10. Select SUMPRODUCT({1,2,3,4}) in the formula bar, press F9, 10 is displayed in formula bar.

3. Sum the Smallest N Values in Excel with User Defined Function with VBA Code

You can create a user-defined function in Excel to sum the smallest N values in a range of cells. Here are the steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: Click Insert > Module to insert a new module.

Step3: Copy and paste the following code into the module. Save the module and close the Visual Basic Editor.

Function SumSmallestN_Excelhow(rng As Range, n As Integer) As Double
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim temp As Double
    Dim sum As Double
    
    For i = 1 To n
        For j = i To rng.Cells.count - 1
            If rng.Cells(j).Value < rng.Cells(i).Value Then
                temp = rng.Cells(i).Value
                rng.Cells(i).Value = rng.Cells(j).Value
                rng.Cells(j).Value = temp
            End If
        Next j
        sum = sum + rng.Cells(i).Value
    Next i
    
    SumSmallestN_Excelhow = sum
End Function

Step4: enter the formula into a blank cell:

=SumSmallestN_Excelhow(A2:A11,4)

Where A2:A11 is the range of cells you want to sum and 4 is the number of smallest values you want to sum.

Step5: Press Enter to calculate the sum of the smallest 4 values in the range.

4. Video: Sum the Smallest N Values in Excel

This video will demonstrate how to sum the smallest N values in Excel using a formula or  a User Defined Function with VBA code as the formula.

5. Related Functions

• Excel SUMPRODUCT function
  The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel INDIRECT function
  The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….
• Excel ROW function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel SMALL function
  The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel SUM function
  The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)