Explanation
In this example, the goal is to sum the smallest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n smallest values from the data set and (2) sum the extracted values. This problem can be solved with the SMALL function together with the SUMPRODUCT function, as explained below. For convenience only, the range B5:B16 is named “data”.
SMALL function
The SMALL function is designed to return the nth smallest value in a range. For example:
=SMALL(range,1) // 1st smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
Normally, SMALL returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to SMALL as the second argument, k , SMALL will return an array of results instead of a single result. For example:
=SMALL(A1:A10,{1,2,3})
will return the 1st, 2nd, and 3rd smallest values in the range A1:A10.
Example
In the example shown, the formula in E5 is:
=SUMPRODUCT(SMALL(data,{1,2,3}))
Working from the inside out, the SMALL function is configured to return the 3 smallest values in the range B5:B16:
=SMALL(data,{1,2,3}) // returns {10,15,20}
Because we provide three separate values for k , the result is an array that contains three results:
{10,15,20}
This array is returned directly to the SUMPRODUCT function :
SUMPRODUCT({10,20,30}) // returns 45
With just a single array to process, SUMPRODUCT sums the values in the array and returns 45 as a final result.
SUM alternative
=SUM(SMALL(data,{1,2,3})) // returns 45
Note: this is an array formula and must be entered with Control + Shift + Enter in Legacy Excel .
When n becomes large
As n becomes a larger number, it becomes tedious to enter longer array constants like {1,2,3,4,5,6,7,8,9,10}, etc. In this situation, you can use a shortcut to create an array that contains sequential numbers automatically based on the ROW and INDIRECT functions. For example, to sum the lowest 10 values in a range, you can use a formula like this:
=SUMPRODUCT(SMALL(range,ROW(INDIRECT("1:10"))))
Here, the INDIRECT function converts the text string “1:10” to the range 1:10, which is returned to the ROW function. The ROW function then returns the 10 row numbers that correspond to the range 1:10 in an array like this:
{1;2;3;4;5;6;7;8;9;10}
Note this is actually a vertical array , as indicated by the semicolons (;) but the SMALL function will happily accept a vertical or horizontal array as the k argument. Once INDIRECT and ROW have been evaluated, the formula is in the same form as before:
=SUMPRODUCT(SMALL(range,{1;2;3;4;5;6;7;8;9;10}) // sum 10 smallest
SMALL will return the 10 lowest values, and SUMPRODUCT will return the sum of these values as a final result.
Variable n
To set up a formula where n is a variable in another cell, you can concatenate inside INDIRECT. For example, if A1 contains n , you can use:
=SUMPRODUCT(SMALL(range,ROW(INDIRECT("1:"&A1))))
This allows a user to change the value of n directly on the worksheet and the formula will respond instantly.
With the SEQUENCE function
New in Excel 365 , the SEQUENCE function can generate numeric arrays directly in one step, which eliminates the need for the ROW + INDIRECT combination explained above. In fact, with SEQUENCE there is really no need to use array constant either. We can simplify the formula as follows:
=SUM(SMALL(range,SEQUENCE(3)) // sum lowest 3 values
=SUM(SMALL(range,SEQUENCE(9)) // sum lowest 9 values
Explanation
In this example, the goal is to sum the smallest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three smallest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps:
- Apply criteria to select specific values
- Extract the 3 smallest values
- Sum the 3 extracted values
This problem can be solved with a formula based on the FILTER function , the SMALL function , and the SUM function . For convenience, the range B5:C16 is an Excel Table named “data”. This allows the formula to use structured references .
Note: FILTER is a newer function not available in " Legacy Excel “. See below for an alternative formula that works in older versions of Excel.
Example formula
The final formula in cell F5 is:
=SUM(SMALL(FILTER(data[Values],data[Group]="A"),{1,2,3}))
To explain how this formula works, we’ll walk through the steps listed above. This means we will be working through the formula from the inside out. This is typical of Excel formulas where one function is nested inside another.
Apply criteria
The first step in the problem is to apply criteria to select values by group. This can be done with the FILTER function . To select values in group “A”, we can use FILTER like this:
FILTER(data[Values],data[Group]="A")
In this formula array is provided as the Values column in the table, and the include argument provides logic to select only values in group “A”. The result is a subset of the values where the group is “A”, which is returned as an array like this:
{10;65;25;45;20;15}
If you are new to the FILTER function, see this video: Basic FILTER function example
Extract 3 smallest values
The next step in the problem is to extract the three smallest values from the array returned by FILTER. For this, we use the SMALL function. The SMALL function is designed to return the nth smallest value in a range. For example:
=SMALL(range,1) // 1st smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
Normally, SMALL returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to SMALL as the second argument, k , SMALL will return an array of results instead of a single result. For example:
=SMALL(range,{1,2,3}) // smallest 3 values
Picking up where we left off, FILTER is used to collect values in group “A”. The results returned by FILTER are returned directly to the SMALL function like this:
SMALL({10;65;25;45;20;15},{1,2,3}) // returns {10,15,20}
The SMALL function then returns the 3 smallest values in an array: 10, 15, 20.
Sum smallest values
The final step in the problem is to sum the values extracted by the SMALL function. This is done with the SUM function :
=SUM({10,15,20})
SUM returns 45 as a final result, which is the sum of the smallest 3 values in group A.
When n becomes large
As n becomes a larger number, it becomes tedious to enter longer array constants like {1,2,3,4,5,6,7,8,9,10}, etc. In this case, you can use the SEQUENCE function to generate an array of sequential numbers automatically like this:
=SUM(SMALL(FILTER(values,criteria),SEQUENCE(n))
Just replace n with the number of smallest values you want to extract:
=SUM(SMALL(FILTER(values,criteria),SEQUENCE(3)) // smallest 3
=SUM(SMALL(FILTER(values,criteria),SEQUENCE(5)) // smallest 5
Legacy Excel
In older versions of Excel that don’t provide the FILTER function, you can use the IF function to “filter” data like this:
=SUM(SMALL(IF(data[Group]="A",data[Values]),{1,2,3}))
The behavior of this formula is much the same as the original formula above. The main difference is that the IF function returns values from group A when the group=“A”, but it returns FALSE when the group does not match the supplied criteria. So the SMALL function receives an array from IF that looks like this:
{10;FALSE;65;FALSE;25;FALSE;45;FALSE;20;FALSE;15;FALSE}
Unlike the FILTER function, which returned just the six values associated with group A, IF returns an array that includes twelve results, one for each value in the original data. However, because SMALL is programmed to automatically ignore the logical values TRUE and FALSE, the result from SMALL, {10,15,20}, is the same as before and the final result is correct:
=SUM({10,15,20}) // returns 45
Note: this is an array formula and must be entered with Control + Shift + Enter in Legacy Excel .
