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.

Table of Contents

## 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],…)