Sum cells that contain even numbers
To sum cells that only contain even numbers we need to use a combination of Excel SUMPRODUCT and MOD functions
Example: Sum cells that contain even numbers
|
=SUMPRODUCT(--(MOD(B5:B9,2)=0),B5:B9)
|
The formula uses the Excel MOD function to return the remainder of 0 for even numbers and 1 for odd numbers. The Excel SUMPRODUCT function then performs this calculation for every cell in the specified range (B5:B9) and sums the values in the same range (B5:B9), which includes two even values (10 and 12).
|
Sub Sum_cells_that_contain_even_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum cells that contain even numbers
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(B5:B9,2)=0),B5:B9)"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(B5:B9,2)=0),B5:B9)"
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.
Values Range: Ensure that the values that you want to test for are captured in range ("B5:B9").
Sum Range: Ensure that the values that you want to sum are captured in range ("B5:B9").
Worksheet Name: Have a worksheet named Analyst.
Values Range: Ensure that the values that you want to test for are captured in range ("B5:B9").
Sum Range: Ensure that the values that you want to sum are captured in range ("B5:B9").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Value Range: Select the values that you want to test for by changing the range ("B5:B9"), range in the MOD function, to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the values that you want to sum by changing the range ("B5:B9"), second part of the formula, to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Value Range: Select the values that you want to test for by changing the range ("B5:B9"), range in the MOD function, to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the values that you want to sum by changing the range ("B5:B9"), second part of the formula, to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum cells that contain even numbers we need to use a combination of Excel SUMPRODUCT and MOD functions. In this example we are summing from the same range that we are testing for even numbers. You can select a different sum range, however, the sum range needs to correspond to the values range.
To sum cells that contain even numbers we need to use a combination of Excel SUMPRODUCT and MOD functions. In this example we are summing from the same range that we are testing for even numbers. You can select a different sum range, however, the sum range needs to correspond to the values range.
FORMULA
=SUMPRODUCT(--(MOD(values_range,2)=0),sum_range)
=SUMPRODUCT(--(MOD(values_range,2)=0),sum_range)
ARGUMENTS
values_range: A numeric range of values that you want to test for even numbers.
sum_range: A numeric range of values that you want to sum that corresponds to the values_range.