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.

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

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

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

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