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

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

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})**

**returns the summation of odd numbers.**

**SUM({0;0;1;1;0;1;0;1;1;0;1;1;1;0})**â€”>**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: **How to Count If Cell Contains Number in Excel

**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 number 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: **Excel Count Number of Cells in Range

**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.

**Download Practice Workbook**

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

**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.