Count cells with specific value in even rows

This tutorial shows how to count cells with specific value in even rows through the use of an Excel formula or VBA

Example: Count cells with specific value in even rows

Count cells with specific value in even rows

METHOD 1. Count cells with specific value in even rows

EXCEL

=SUMPRODUCT(--(MOD(ROW(B8:B14),2)=0),--(B8:B14=C5))
This 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 (B8:B14) that contains the specific value (in this example the cell has to contain a value of 5) and returns the number of occurrences. In this example the formula will return a value of one given there is only one cell in an even row (B8), within the selected range, that contain a value of 5.

METHOD 1. Count cells with specific value in even rows using VBA

VBA

Sub Count_cells_with_specific_value_in_even_rows()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells with specific value in even rows
ws.Range("E8").Formula = "=SUMPRODUCT(--(MOD(ROW(B8:B14),2)=0),--(B8:B14=C5))"

End Sub

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E8") in the VBA code.
Data Range: Select the values that you want to test and count by changing the range ("B8:B14")in the VBA code.
Specific Value: Select the value that you want to test and count for by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet in which you want to count cells with specific value in even rows by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.

Explanation about the formula used to count cells with specific value in even rows

EXPLANATION

EXPLANATION

This tutorial shows how to count cells with specific value in even rows through the use of an Excel formula or VBA.
This tutorial provides one Excel method that can be applied to count the number of cells with a specific value in even rows by using the Excel SUMPRODUCT, MOD and ROW functions.
This tutorial provides one VBA method that can be applied to count the number of cells with a specific value in even rows from a selected range using the VBA formula and the exact same formula that is provided in the Excel method.
FORMULA
=SUMPRODUCT(--(MOD(ROW(range),2)=0),--(range=specific_value))
ARGUMENTS
range: A range of values that you want to test if there are cells in even rows that contain values and count the number of these occurrences.
specific_value: The specific value that you want to count for in the specified range.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to count cells with specific value in odd rows using Excel and VBA methods
How to count cells with specific value in even columns using Excel and VBA methods
How to count cells with specific value in odd columns using Excel and VBA methods
How to count cells with values in odd rows using Excel and VBA methods
How to count cells with values in even rows using Excel and VBA methods

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values
The Excel MOD function returns the remainder after dividing a number by the divisor
The Excel ROW function returns the first row number of the selected reference