How to Count Odd and Even Numbers in Excel (3 Easy Ways)

Method 1 – Combine SUM and MOD Functions to Count Odd and Even Numbers

1.1 Counting Odd Numbers

Steps:

Apply the following formula in your preferred cell.

=SUM(1*(MOD($C$5:$C$18,2)=1))

⧪ Formula Breakdown ⧪

MOD($C$5:$C$18,2)=1 —> fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 1 or not.
Output: {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}

SUM(1*(MOD($C$5:$C$18,2)=1))
SUM(1*{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE})
SUM({0;0;1;1;0;1;0;1;1;0;1;1;1;0}) —> returns the summation of odd numbers.
Output: 8

1.2 Counting Even Numbers

Steps:

Apply the following formula in your preferred cell.

=SUM(1*(MOD($C$5:$C$18,2)=0))

⧪ Formula Breakdown ⧪

MOD($C$5:$C$18,2)=0 —> fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 0 or not.
Output: {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

SUM(1*(MOD($C$5:$C$18,2)=1))
SUM(1*{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})
SUM({1;1;0;0;1;0;1;0;0;1;0;0;0;1}) —> returns the summation of even numbers.
Output: 6

Method 2 – Merge SUMPRODUCT & MOD Functions to Count Odd and Even Numbers

2.1 Count Odd Numbers

Steps:

Apply the following formula in your preferred cell.

=SUMPRODUCT((MOD($C$5:$C$18,2)<>0)+0)

⧪ Formula Breakdown ⧪

MOD($C$5:$C$18,2)<>0 —> fetches remainders when a number is divided by 2 and returns TRUE if the remained is not equal to 0.
Output: {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}

SUMPRODUCT((MOD($C$5:$C$18,2)<>0)+0)
SUMPRODUCT({0;0;1;1;0;1;0;1;1;0;1;1;1;0}) —> returns the summation of odd numbers.
Output: 8

2.2 Count Even Numbers

Steps:

Apply the following formula in your preferred cell.

=SUM(1*(MOD($C$5:$C$18,2)=0))

⧪ Formula Breakdown ⧪

MOD(C5:C18,2)=0 —> fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 0 or not.
Output: {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

SUMPRODUCT((MOD(C5:C18,2)=0)+0)
SUMPRODUCT({1;1;0;0;1;0;1;0;0;1;0;0;0;1}) —> returns the summation of even numbers.
Output: 6

Method 3 – Apply VBA Macro to Count Odd and Even Numbers

Steps:

Press ALT +F11 . Microsoft Visual Basic will open.
From the Toolbar, click on Insert & Select Module.

For Counting Odd Numbers

Add the following VBA Macro Code into the module.

Sub CountCellsContainOddNumbers()
Dim mysheet As Worksheet
Dim myrange As Range
Set mysheet = Worksheets("VBAMethod")
Set myrange = mysheet.Range("C5:C18")
For Each xcell In myrange
cellmod = xcell Mod 2
oddnum = oddnum + cellmod
Next xcell
mysheet.Range("C20") = oddnum
End Sub

Press F5 or click on the Run button to execute the code.

It will output the total number of odd numbers in the selected cell.

For Counting Even Numbers

Add the following VBA Macro Code into the module.

Sub CountCellsContainOddNumbers()
Dim mysheet As Worksheet
Dim myrange As Range
Set mysheet = Worksheets("VBAMethod")
Set myrange = mysheet.Range("C5:C18")
For Each xcell In myrange
cellmod = xcell Mod 2
oddnum = oddnum + cellmod
Next xcell
mysheet.Range("C20") = oddnum
End Sub