# 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``````

• Press  F5  to execute the code.

• It will output the total amount of even numbers.

<< Go Back to Count Cells | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF