**Method 1 – Combine SUM and MOD Functions to Count Odd and Even Numbers**

**1.1 Counting**** Odd**** Numbers**

**Steps:**

- Apply the following formula in your preferred cell.

`=SUM(1*(MOD($C$5:$C$18,2)=1))`

**⧪ Formula Breakdown ⧪**

**MOD($C$5:$C$18,2)=1** **—> **fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 1 or not.

**Output:** *{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}*

**SUM(1*(MOD($C$5:$C$18,2)=1))
**

**SUM(1*{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE})**

**returns the summation of odd numbers.**

**SUM({0;0;1;1;0;1;0;1;1;0;1;1;1;0})**—>**Output:**

*8*

**1.2 Counting Even Numbers**

**Steps:**

- Apply the following formula in your preferred cell.

`=SUM(1*(MOD($C$5:$C$18,2)=0))`

**⧪ Formula Breakdown ⧪**

**MOD($C$5:$C$18,2)=0** **—> **fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 0 or not.

**Output:** *{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*

**SUM(1*(MOD($C$5:$C$18,2)=1))
**

**SUM(1*{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})**

**SUM({1;1;0;0;1;0;1;0;0;1;0;0;0;1}) —>**returns the summation of even numbers.

**Output:**

*6*

**Method 2 – Merge SUMPRODUCT & MOD Functions to Count Odd and Even Numbers**

**2.1 Count Odd Numbers**

**Steps:**

- Apply the following formula in your preferred cell.

`=SUMPRODUCT((MOD($C$5:$C$18,2)<>0)+0)`

**⧪ Formula Breakdown ⧪**

**MOD($C$5:$C$18,2)<>0** **—> **fetches remainders when a number is divided by 2 and returns TRUE if the remained is not equal to 0.

**Output:** *{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}*

**SUMPRODUCT((MOD($C$5:$C$18,2)<>0)+0)
**

**SUMPRODUCT({0;0;1;1;0;1;0;1;1;0;1;1;1;0}) —>**returns the summation of odd numbers.

**Output:**8

**2.2 Count Even Numbers**

**Steps:**

- Apply the following formula in your preferred cell.

`=SUM(1*(MOD($C$5:$C$18,2)=0))`

**⧪ Formula Breakdown ⧪**

**MOD(C5:C18,2)=0** **—> **fetches remainders when a number is divided by another number(i.e. 2) and compares whether the remainder is equal to 0 or not.

**Output:** *{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*

**SUMPRODUCT((MOD(C5:C18,2)=0)+0)
**

**SUMPRODUCT({1;1;0;0;1;0;1;0;0;1;0;0;0;1}) —>**returns the summation of even numbers.

**Output:**

*6*

**Method 3 – Apply VBA Macro to Count Odd and Even Numbers**

**Steps:**

- Press
**ALT +F11**.**Microsoft Visual Basic**will open. - From the Toolbar, click on
**Insert**& Select**Module**.

**For Counting Odd Numbers**

- Add the following VBA Macro Code into the module.

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

- Press
**F5**or click on the**Run**button to execute the code.

- It will output the total number of odd numbers in the selected cell.

**For Counting Even Numbers**

- Add the following VBA Macro Code into the module.

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

- Press
**F5**to execute the code.

- It will output the total amount of even numbers.

**Download Practice Workbook**

**<< Go Back to Count Cells | Formula List | Learn Excel**