Summing is one of the most powerful use of Excel. However, SUM function doesn’t perform all the summing the way we want. For example, when we want to sum the bottom n values in Excel. You can sum those values by using + but it can cost you a few hours if you have a long list to add up.
In this article, I will show you how to sum the smallest N values in Excel. The value will automatically change when new value is added to the list. The flexibility allows you to avoid making mistakes and save you time. I will provide you with 2 formulas which you can choose to use either one according to your situation.
If you have the following questions, I suggest you reading this until the end.
- How to sum the smallest N values in Excel?
- How to sum the bottom N values in Excel?
- How do I find the Nth smallest in Excel and sum them?
You might also be interested in How to get value from every nth row?
Example
In the above example, the cell B2, cell B9 and cell 15 contains the smallest 3 values on the list. The sum of these 3 cells is 60. We can achieve the same result by using 2 formulas. Next I will show you how to get the result by different formula.
What we would like to do
To sum the smallest 3 values
You might also be interested in How to prevent duplicate entries in Excel?
Sum the Smallest N Values by formula with SUM
Formula in cell D5
=SUM(SMALL(B2:B16,{1,2,3}))Attention! This is an array formula. To use an array formula, you will have to press CTRL+SHIFT+ENTER instead of simply ENTER.
If you choose not to use an array formula, please scroll down to the next section to check out a normal formula.
How to use the formula
=SUM(SMALL(Cell_range,{Nth,Nth,Nth,...}))In the above example, the list starts from cell B2 and stop at cell B16. So we will put B12:B16 as the Cell_range.
Since we would like to sum the smallest 3 values, we would put 1, 2 and 3 in the “Nth” field.
By changing “Nth” value, you can sum up any smallest N value as you like.
For example, if I would like to sum up smallest 5 values, the formula would have been as below.
=SUM(SMALL(B2:B16,{1,2,3,4,5}))You can twist the nth and apply it to your own situation.
How does this formula work
Here I will divide the formula into different part and explain it thoroughly.
SMALL function
SMALL(Cell_range,{Nth,Nth,Nth,...}))SMALL function returns the k-smallest value in a data set. For example, the fifth smallest number.
Here we are using SMALL to find the 3rd smallest number on the list, which is cell B9 (27), cell 15 (15) and cell B2 (18).
SUM function
SUM function adds all the numbers in a range of cells.
=SUM(SMALL(Cell_range,{Nth,Nth,Nth,...}))SUM function helps us to sum up the values contained in cell B9 (27), cell 15 (15) and cell B2 (18) and returns the result (60).
You might also be interested in How to Merge Cells Across Multiple Rows/Columns.
Sum the Smallest N Values by formula with SUMPRODUCT
Formula in cell D11
=SUMPRODUCT(SMALL(B2:B16,{1,2,3}))How to use the formula
=SUMPRODUCT(SMALL(Cell_range,{Nth,Nth,Nth,...}))In the above example, the list starts from cell B2 and stop at cell B16. So we will put B12:B16 as the Cell_range.
Since we would like to sum the smallest 3 values, we would put 1, 2 and 3 in the “Nth” field.
By changing “Nth” value, you can sum up any smallest N value as you like.
For example, if I would like to sum up smallest 5 values, the formula would have been as below.
=SUMPRODUCT(SMALL(B2:B16,{1,2,3,4,5}))You can twist the nth and apply it to your own situation.
How does this formula work
Here I will divide the formula into different part and explain it thoroughly.
SMALL function
SMALL(Cell_range,{Nth,Nth,Nth,...}))SMALL function returns the k-smallest value in a data set. For example, the fifth smallest number.
Here we are using SMALL to find the 3rd smallest number on the list, which is cell B9 (27), cell 15 (15) and cell B2 (18).
SUMPRODUCT function
SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.
=SUMPRODUCT(SMALL(Cell_range,{Nth,Nth,Nth,...}))SUMPRODUCT function helps us to sum up the values contained in cell B9 (27), cell 15 (15) and cell B2 (18) and returns the result (60).
To those who read explanation for both formula may wonder the difference between them.
Actually the only difference is that SUM function is not designed for dealing with array. On the contrary, SUMPRODUCT is designated for summing an array of values.
Some people may prefer SUMPRODUCT over SUM because array formula sometimes slow down the excel worksheet calculation.
It’s totally up to you which one to use.
You might also be interested in How To Remove Digits After Decimal In Excel?.
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
