This problem can be solved with the SMALL function together with the SUMPRODUCT function, as explained below.
For convenience only, the range B5:B16 isnamed"data".
SMALL function
TheSMALL functionis designed to return thenthsmallest value in a range.
For example:
Normally, SMALL returns just one value.
However, if you supply anarray constant(e.g.
Variable n
To set up a formula wherenis a variable in another cell, you canconcatenateinside INDIRECT.
In fact, with SEQUENCE there is really no need to use array constant either.
Read more about SUMPRODUCT and arrayshere.
from a set of numeric data.
SUM Function
The Excel SUM function returns the sum of values supplied.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
