Sum cells with values in even rows
To sum cells with values in even rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions
Example: Sum cells with values in even rows
|
=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=0),(B5:B11))
|
The formula uses the Excel ROW and MOD functions to return the remainder of 0 for a cell in an even row and 1 for a cell in an odd 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 0 (refers to cells in an even row) which includes two even values (5 and 24 from cells B8 and B10, respectively). Therefore, the formula will return the sum of these values which equate to 29.
|
SubSum_cells_with_values_in_even_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 even rows
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=0),(B5:B11))"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(ROW(B5:B11),2)=0),(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 even rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions.
To sum cells with values in even rows you need to apply a combination of Excel SUMPRODUCT, MOD and ROW functions.
FORMULA
=SUMPRODUCT(--(MOD(ROW(range),2)=0),(range))
=SUMPRODUCT(--(MOD(ROW(range),2)=0),(range))
ARGUMENTS
range: A numeric range of values that you want to test and sum if there are cells in even rows that contain values.