Sum largest n numbers
To sum the largest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the largest n numbers to be summed and the other can be referenced to a cell
Example: Sum largest n numbers
|
The formula returns the sum of the four largest numbers in range (C8:C14). The formula requires you to manually input the number of largest 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 largest numbers in the range. If you want to sum the five largest 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 largest n numbers using cell reference
EXCEL
|
=SUMPRODUCT(LARGE(C8:C14,ROW(INDIRECT("1:"&C5))))
|
The formula returns the sum of the four largest number in range (C8:C14). The formula references to a specific cell that contains the value that represents the number of largest numbers to sum. This formula allows greater flexibility to change the number of n largest numbers that you want to sum, without directly changing the formula.
|
Dim ws As Worksheet
ws.Range("F7").Formula = "=SUM(LARGE(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 largest numbers from range ("C8:C14"). Therefore, if 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 largest numbers: Select the n largest numbers to sum by changing the reference {1, 2, 3, 4} in the VBA code.
METHOD 2. Sum largest n numbers using cell reference
VBA
Dim ws As Worksheet
ws.Range("F7").Formula = "=SUMPRODUCT(LARGE(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 largest numbers from range ("C8:C14"). Therefore, if using the exact VBA code, at the very least this range needs to capture all of the values that you want sum.
n largest numbers: In this example we are summing the four largest numbers from a selected range. This number is sourced from cell ("C5"), therefore if using the exact VBA code cell ("C5") needs to contain the amount of largest 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 largest numbers: Select the n largest 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 largest numbers to sum.
To sum the largest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the largest n numbers to be summed and the other can be referenced to a cell.
=SUM(LARGE(range,{1, …, n}))
=SUMPRODUCT(LARGE(range,ROW(INDIRECT("1:"&num))))
ARGUMENTS
range: The range that captures the numbers to be summed.
{1, …, n}: Numbers that represents the largest numbers to be summed.
num: A number that represents the largest numbers to be summed.