How to Count Odd or Even Numbers only in Excel

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

How to count odd numbers only

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

How to count even numbers only in Excel

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.

How to color odd numbers only using conditional formatting

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

color odd numbers in Excel

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

How to color even numbers only in Excel

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

    How to Create a Gauge Chart in Excel: SPEEDOMETER

    How to add a Header in Excel: Simple Guide

    Leave a Reply

    Discover more from Excel Wizard

    Subscribe now to keep reading and get access to the full archive.

    Continue Reading