# 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. ## 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. Step 2: As it is an array formula, press CTRL+SHIFT+ENTER. 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. Step 2: Press CTRL+SHIFT+ENTER. 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. Step 2: Press ENTER. #### 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. Step 2: Press ENTER ### 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. 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
`````` Step 4: Press F5 to run the code. 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`````` Step 3: Press F5 to run the code. 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 