Sometimes, we might face the urge to count odd and even numbers in **Microsoft Excel** to understand the entries. In this article, I will explain 3 smart ways to **count odd and even numbers**** in Excel**. I hope it will be very helpful for you if you are looking for an efficient way to do so.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

**3 Smart Ways to Count Odd and Even Numbers in Excel**

In this article, we’ll describe ways to count odd and even numbers using combinations of functions such as **SUM** & **MOD**, **SUMPRODUCT **& **MOD, **and** VBA Macro**. In a scenario, we have a student’s information in the **Registration Number** and **Student Name **columns and we want to Count Odd and Even numbers in the dataset. Let’s dive into the details.

**1. Combine SUM and MOD Functions to Count Odd and Even Numbers**

In order to count odd and even numbers, we can use a formula that is a combination of **SUM **and **MOD **functions. Let’s follow the following procedures for details.

**1.1 Counting**** Odd**** Numbers**

**Steps:**

- Apply the following formula in your preferred cell for the count of odd numbers.

`=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:**

- Insert the following formula in your desired location to have the desired output.

`=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*

**Read More:**** Excel Count Cells with Numbers (5 Simple Ways)**

**2. Merge SUMPRODUCT & MOD Functions to Count Odd and Even Numbers**

**The MOD function **can also be merged with **the SUMPRODUCT function **to count odd and even numbers. Follow the steps mentioned below for more details.

**2.1 Count Odd Numbers**

**Steps:**

- First of all, pick a cell and input the following formula in that cell to have the amount of odd numbers.

`=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:**

- Insert the following formula to have the amount of even numbers in your desired location.

`=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*

**Read More:**** How to Count Non Blank Cells with Condition in Excel (6 Methods)**

**Similar Readings**

**How to Count Only Visible Cells in Excel (5 Tricks)****Count Filled Cells in Excel Using VBA (7 Methods)****How to Count Number of Cells with Dates in Excel (6 Ways)****Count Cells that Contain Specific Text in Excel****How to Count Cells That are Not Blank in Excel (8 Useful Methods)**

**3. Apply VBA Macro to Count Odd and Even Numbers**

The smartest way to have the total amount of odd and even numbers is to apply the **VBA ****Macro**. It’s actually not that hard. You will know it after reading the following section.

**Steps:**

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

**For Counting Odd Numbers**

- Write the below
**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
```

- Next, press
**F5**or click on the**Run**button to execute the code.

- You will have the total amount of odd numbers in your selected cell.

**For Counting Even Numbers**

- Input the below
**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.

- Thus, we will have the total amount of even numbers.

**Read More: How to Count If Cell Contains Number in Excel (5 Smart Ways)**

**Conclusion**

At the end of this article, I like to add that I have tried to explain 3 smart ways to **count odd and even numbers**** in Excel**. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using **Excel**.

## Related Articles

**How to Count Cells with Specific Value in Excel (5 Easy Methods)****How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)****Excel Count Number of Cells in Range (6 Easy Ways)****How to Count Blank Cells with Condition in Excel (4 Easy Methods)****How to Count Filled Cells in Excel (5 Quick Ways)****Excel Formula to Count Cells with Text (All Criteria Included)**