Excel is an excellent tool for counting odd or even numbers. It has inbuilt formulas and features that can differentiate between an even and an odd number. Therefore, if you struggle to sum or count odd numbers or even numbers, only this guide is for you.

## Count odd numbers only using the MOD function

**MOD **is a perfect formula to use because it returns remainders after a division operation.

An odd number is simply a number that cannot be divided by two without a remainder. These are usually numbers that have 1,3,5,7, and 9 in one place.

Here is how to use MOD

1. Select a cell where you wish to display the number of cells containing od numbers

2. Now enter this formula to that cell **=SUMPRODUCT(–(MOD(E2:E15,2)=1))**

3. Note that you must replace the range E2:E15 with the range matching your spreadsheet.

4. Press enter. The number of cells containing odd numbers will be displayed.

**Download the Excel file use to follow along**

## Count Even numbers only Using the MOD function

An even number is a number that can be divided by two without a remainder. You can also refer to it as a multiple of 2.

1. Select a cell where you wish to display the number of cells containing even numbers

2. Now enter this formula to that cell **=SUMPRODUCT(–(MOD(E2:E15,2)=0))**

3. Note that you must replace the range E2:E15 with the range matching your spreadsheet.

4. Press enter

5. The number of cells containing even numbers in that range will be displayed.

## How to highlight Odd numbers only using Conditional Formatting

Conditional formatting helps to visualize things faster. You can easily identify your spreadsheet’s **odd numbers only** by applying conditional formatting. Here are the steps to follow.

1. Select or highlight the cells you wish to format

2. Under the home tab, navigate to the styles group and click on conditional formatting

3. A drop-down menu will be launched. Click on the **new rule**

4. Now click on **“Use a formula to determine which cells to format” **

5. Type the following formula in the edit rule description area

**=MOD(C2,2)=1**

Note that C2 is the first cell of the data you highlighted. You can change it according to your spreadsheet.

6. Now click on **Format** >**Fill** to select a color to **highlight all the odd numbers** in the highlighted data.

7. Press ok, and all the odd numbers will be highlighted in green.

## How to highlight Even numbers only using conditional formatting

You can also color even numbers only in Excel with the help of conditional formatting. Follow these simple steps.

1. Select or highlight the cells you wish to format

2. Under the home tab, navigate to the styles group and click on conditional formatting

3. A drop-down menu will be launched. Click on the **new rule**

4. Now click on **“Use a formula to determine which cells to format” **

5. Type the following formula in the edit rule description area

**=MOD(C2,2)=0**

6. Now click on format and select a suitable color for the even numbers

7. Press ok. Finally, all the even numbers will be colored with your selected color.

## Use VBA code to count odd and Even numbers only

Excel macros allow users to develop their own functions to solve problems or complete tasks easily. You can use the VBA code to count odd or even numbers.

Here are the steps

### VBA to count Even numbers

1. Open the Excel file containing even numbers

2. Open the VBA editor by using the shortcut **ALT+11 **

3. Click on insert>module

4. Copy and paste the following VBA code into the VBA editor

Sub CountEvenNumbersInRange() Dim rng As Range Dim cell As Range Dim evenCount As Long Dim inputRange As Range ' Prompt user to enter the range On Error Resume Next Set inputRange = Application.InputBox("Enter the range (e.g., D2:D15)", Type:=8) On Error GoTo 0 ' Check if inputRange is not empty and valid If Not inputRange Is Nothing Then evenCount = 0 For Each cell In inputRange If IsNumeric(cell.Value) And cell.Value Mod 2 = 0 Then evenCount = evenCount + 1 End If Next cell MsgBox "Number of even numbers in the range: " & evenCount End If End Sub

4. Click F5 to run the code

5. A prompt will pop up asking you to enter the range

Type in the range and press okay.

A new pop-up will appear, displaying the number of odd numbers in a given range.

### VBA code to count odd numbers

1. Open the Excel file containing even numbers

2. Open the VBA editor by using the shortcut **ALT+11 **

3. Click on insert>module

4. Copy and paste the following VBA code into the VBA editor

Sub CountOddNumbersInRange() Dim rng As Range Dim cell As Range Dim oddCount As Long Dim inputRange As Range ' Prompt user to enter the range On Error Resume Next Set inputRange = Application.InputBox("Enter the range (e.g., D2:D15)", Type:=8) On Error GoTo 0 ' Check if inputRange is not empty and valid If Not inputRange Is Nothing Then oddCount = 0 For Each cell In inputRange If IsNumeric(cell.Value) And cell.Value Mod 2 <> 0 Then oddCount = oddCount + 1 End If Next cell MsgBox "Number of odd numbers in the range: " & oddCount End If End Sub

5. You will be prompted to enter the range of your data

6. Press okay

7. The number of od numbers in the range will be displayed.

Read Similar Tutorials