Sum cells with values in odd rows
To sum cells with values in odd rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions
Example: Sum cells with values in odd rows
|
=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=1),(B5:B11))
|
The formula uses the Excel ROW and MOD functions to return the remainder of 1 for a cell in an odd row and 0 for a cell in an even row. The Excel SUMPRODUCT function then performs this calculation for every cell in the specified range (B5:B11) and returns the sum of all the cells that have been identified to have a remainder of 1 (refers to cells in an odd row) which includes three odd values (15, 12 and 18 from cells B5, B9 and B11, respectively). Therefore, the formula will return the sum of these values which equate to 45.
|
SubSum_cells_with_values_in_odd_rows()
'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 rows
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=1),(B5:B11))"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=1),(B5:B11))"
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 ("B5:B11").
Worksheet Name: Have a worksheet named Analyst.
Values Range: Ensure that the values that you want to test and sum are captured in range ("B5:B11").
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 ("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 ("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 ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum cells with values in odd rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions.
To sum cells with values in odd rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions.
FORMULA
=SUMPRODUCT(--(MOD(ROW(range),2)=1),(range))
=SUMPRODUCT(--(MOD(ROW(range),2)=1),(range))
ARGUMENTS
range: A numeric range of values that you want to test and sum if there are cells in odd rows that contain values.