Sum values if cells are not blank
To sum value if cells are not blank you can apply the Excel SUMIF function
Example: Sum values if cells are not blank
|
=SUMIF(B5:B11,"<>",C5:C11)
|
The formula uses the Excel SUMIF function to sum the numbers that do not have a blank cell in the corresponding cells in range (B5:B11).
|
Sub Sum_values_if_cells_are_not_blank()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum values if cells are not blank
ws.Range("E5") = Application.WorksheetFunction.SumIf(ws.Range("B5:B11"), "<>", ws.Range("C5:C11"))
ws.Range("E5") = Application.WorksheetFunction.SumIf(ws.Range("B5:B11"), "<>", ws.Range("C5:C11"))
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.
Sum Range: Ensure that the data you want sum is captured in range ("C5:C11").
Range: Ensure that the corresponding range to the sum range is captured in range ("B5:B11").
Worksheet Name: Have a worksheet named Analyst.
Sum Range: Ensure that the data you want sum is captured in range ("C5:C11").
Range: Ensure that the corresponding range to the sum range is captured in range ("B5:B11").
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.
Sum Range: Select the range that you want to sum by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the sum range by changing the range ("B5:B11") 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.
Sum Range: Select the range that you want to sum by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the sum range by changing the range ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum value if cells are not blank you can apply the Excel SUMIF function.
To sum value if cells are not blank you can apply the Excel SUMIF function.
FORMULA
=SUMIF(range, "<>", sum_range)
=SUMIF(range, "<>", sum_range)
ARGUMENTS
range: The range of cells you want to test the criteria against.
"<>": The criteria that is used to determine which of the cells, from the specified range, should be summed.
sum_range: The range of cells you want to sum from.