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
