Sum cells with values in even columns
To sum cells with values in even columns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions
Example: Sum cells with values in even columns
|
=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=0),(C5:I5))
|
The formula uses the Excel COLUMN and MOD functions to return the remainder of 0 for a cell in an even column and 1 for a cell in an odd column. The Excel SUMPRODUCT function then performs this calculation for every cell in the specified range (C5:I5) and returns the sum of all the cells that have been identified to have a remainder of 0 (refers to cells in an even column) which includes two even values (5 and 24 from cells F5 and H5, respectively). Therefore, the formula will return the sum of these values which equate to 29.
|
SubSum_cells_with_values_in_even_columns()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum cells with values in even columns
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=0),(C5:I5))"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=0),(C5:I5))"
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 and sum are captured in range ("C5:I5").
Worksheet Name: Have a worksheet named Analyst.
Values Range: Ensure that the values that you want to test and sum are captured in range ("C5:I5").
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 and sum by changing the range ("C5:I5") 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 and sum by changing the range ("C5:I5") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum cells with values in even columns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions.
To sum cells with values in even columns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions.
FORMULA
=SUMPRODUCT(--(MOD(COLUMN(range),2)=0),(range))
=SUMPRODUCT(--(MOD(COLUMN(range),2)=0),(range))
ARGUMENTS
range: A numeric range of values that you want to test and sum if there are cells in even columns that contain values.