Sum only negative numbers
To sum only negative numbers in a specified range you can apply the Excel SUMIF function
Example: Sum only negative numbers
|
=SUMIF(C5:C11,"<0")
|
The formula uses the Excel SUMIF function to sum only the negative numbers from the specified range (C5:C11).
|
Sub Sum_only_negative_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum only negative numbers
ws.Range("E5") = Application.WorksheetFunction.SumIf(ws.Range("C5:C11"), "<0")
ws.Range("E5") = Application.WorksheetFunction.SumIf(ws.Range("C5:C11"), "<0")
End Sub
OBJECTS
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.
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.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Range: Ensure that the data you want sum and test against is captured in range ("C5:C11").
Worksheet Name: Have a worksheet named Analyst.
Range: Ensure that the data you want sum and test against is captured in range ("C5:C11").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to sum and test against by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to sum and test against by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum only negative numbers in a specified range you can apply the Excel SUMIF function.
To sum only negative numbers in a specified range you can apply the Excel SUMIF function.
FORMULA
=SUMIF(range, "<0")
=SUMIF(range, "<0")
ARGUMENTS
range: The range of cells you want to sum from and test the criteria against.
"<0": The criteria that is used to determine which of the cells, from the specified range, should be summed.