Sum cells with values in odd columns
To sum cells with values in odd columns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions
Example: Sum cells with values in odd columns
|
=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),(C5:I5))
|
The formula uses the Excel COLUMN and MOD functions to return the remainder of 1 for a cell in an odd column and 0 for a cell in an even 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 1 (refers to cells in an odd column) which includes three odd values (15, 12 and 18 from cells C5, G5 and I5, respectively). Therefore, the formula will return the sum of these values which equate to 45.
|
SubSum_cells_with_values_in_odd_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 odd columns
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),(C5:I5))"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),(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 odd coliumns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions.
To sum cells with values in odd coliumns you need to apply a combination of Excel SUMPRODUCT, MOD and COLUMN functions.
FORMULA
=SUMPRODUCT(--(MOD(COLUMN(range),2)=1),(range))
=SUMPRODUCT(--(MOD(COLUMN(range),2)=1),(range))
ARGUMENTS
range: A numeric range of values that you want to test and sum if there are cells in odd columns that contain values.