Sum smallest n numbers
To sum the smallest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the smallest n numbers to be summed and the other can be referenced to a cell
Example: Sum smallest n numbers
|
The formula returns the sum of the four smallest numbers in range (C8:C14). The formula requires you to manually input the number of smallest numbers that you want to sum. In this example we have entered {1, 2, 3, 4} which will sum the first, second, third and fourth smallest numbers in the range. If you want to sum the five smallest numbers in range (C8:C14) you will need to manually update the formula by replacing {1, 2, 3, 4} with {1, 2, 3, 4, 5}.
|
METHOD 2. Sum smallest n numbers using cell reference
EXCEL
|
=SUMPRODUCT(SMALL(C8:C14,ROW(INDIRECT("1:"&C5))))
|
The formula returns the sum of the four smallest number in range (C8:C14). The formula references to a specific cell that contains the value that represents the number of smallest numbers to sum. This formula allows greater flexibility to change the number of n smallest numbers that you want to sum, without directly changing the formula.
|
Dim ws As Worksheet
ws.Range("F7").Formula = "=SUM(SMALL(C8:C14,{1,2,3,4}))"
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are summing the four smallest numbers from range ("C8:C14"). Therefore, is using the exact VBA code, at the very least this range needs to capture all of the values that you want sum.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that captures at the very least the numbers that you want to sum by changing the range ("C8:C14") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
n smallest numbers: Select the n smallest numbers to sum by changing the reference {1, 2, 3, 4} in the VBA code to.
METHOD 2. Sum smallest n numbers using cell reference
VBA
Dim ws As Worksheet
ws.Range("F7").Formula = "=SUMPRODUCT(SMALL(C8:C14,ROW(INDIRECT(""1:""&C5))))"
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are summing the four smallest numbers from range ("C8:C14"). Therefore, is using the exact VBA code, at the very least this range needs to capture all of the values that you want sum.
n smallest numbers: In this example we are summing the four smallest numbers from a selected range. This number is sourced from cell ("C5"), therefore is using the exact VBA code cell ("C5") needs to contain the amount of smallest numbers to sum.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that captures at the very least the numbers that you want to sum by changing the range ("C8:C14") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
n smallest numbers: Select the n smallest numbers to sum by changing the value in cell ("C5") or change the cell reference ("C5") in the VBA code to any cell that contains the amount of smallest numbers to sum.
To sum the smallest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the smallest n numbers to be summed and the other can be referenced to a cell.
=SUM(SMALL(range,{1, …, n}))
=SUMPRODUCT(SMALL(range,ROW(INDIRECT("1:"&num))))
ARGUMENTS
range: The range that captures the numbers to be summed.
{1, …, n}: Numbers that represents the smallest numbers to be summed.
num: A number that represents the smallest numbers to be summed.