How to Count Odd and Even Numbers in Excel (3 Easy Ways)

Sometimes in a dataset, we have to count odd and even numbers in Excel to understand the entries. 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 Code.

In a scenario, we have a student’s Registration Number Dataset and we want to Count Odd and Even numbers in the dataset.

dataset

Dataset for Download

3 Easy Methods to Count Odd and Even Numbers in Excel

Method 1: Using SUM & MOD Array Function

The MOD Function fetches remainders when a number is divided by another number. For instance, MOD(odd/even,2) fetches 1 or 0 as the remainder of odd or even numbers. Therefore, we use the MOD function to justify any number as even or odd.

Method 1.1: For Counting Odd Number

Step 1: Type the Formula =SUM(1*(MOD($C$8:$C$21,2)=1)) in any cell desired cell.

sum and mod

Step 2: As it is an array formula, press CTRL+SHIFT+ENTER.

result sum and mod

There are 8 Odd Numbers in the dataset so the formula provided that many as the outcome.

Method 1.2: For Counting Even Number

Step 1: Insert the Formula =SUM(1*(MOD($C$8:$C$21,2)=0)) in any cell desired cell.

 sum and mod even

Step 2: Press CTRL+SHIFT+ENTER.

final sum and mod even

The formula provided 6 as the result since we can see there are 6 Even Numbers in the dataset.

Method 2: Using SUMPRODUCT & MOD Function

Method 2.1: For Counting Odd Number

Step 1: Type the Formula =SUMPRODUCT((MOD($C$8:$C$21,2)<>0)+0) in any cell desired cell.

sumproduct and mod odd

Step 2: Press ENTER.

 sum and mod odd

Method 2.2: For Counting Even Number

Step 1: Insert the Formula =SUMPRODUCT((MOD($C$8:$C$21,2)=0)+0) in any cell desired cell.

sumproduct and mod even

Step 2: Press ENTER

sumproduct and mod even

Method 3: Using VBA Macro Code

Method 3.1: For Counting Odd Number

Step 1: Press ALT +F11 altogether. Microsoft Visual Basic will open.

Step 2: From the Toolbar click on Insert & Select Module.

vba method

Step 3: Paste the below VBA Macro Code in the module

Sub CountCellsContainOddNumbers()
Dim mysheet As Worksheet
Dim myrange As Range
Set mysheet = Worksheets("VBAMethod")
Set myrange = mysheet.Range("C7:C20")
For Each xcell In myrange
cellmod = xcell Mod 2
oddnum = oddnum + cellmod
Next xcell
mysheet.Range("C2") = oddnum
For Each xcell In myrange
End Sub

vba code odd number

Step 4: Press F5 to run the code.

vba method

The count of Odd Numbers shows up in Cell C2 as we type in the VBA Macro Code.

Method 3.2: For Counting Even Number

Step 1: Repeat Steps 1&2 mentioned in the above method.

Step 2: Paste below VBA Macro Code

Sub CountCellsContainEvenNumbers() 
Dim mysheet As Worksheet 
Dim myrange As Range 
Set mysheet = Worksheets("VBAMethod") 
Set myrange = mysheet.Range("C7:C20") 
evennum = 0 
For Each xcell In myrange 
If xcell Mod 2 = 0 Then 
evennum = evennum + 1 
End If 
Next xcell 
mysheet.Range("C3") = evennum 
End Sub

vba even number

Step 3: Press F5 to run the code.

vba method even

The count of Even Numbers shows up in Cell C3 as we type in the VBA Macro Code.

Conclusion

You can also show individually any number in the adjacent cell whether it is an odd or even in Print Text. In this article, we describe the methods that can simply count the number respective of their being odd or even. We use the MOD function combined with SUM and SUMPRODUCT and VBA Macro Code to count odd and even numbers. Hope you find the methods work worthy and easy to follow. Comment, if you need further clarifications and have something to add.

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo