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

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})
SUM({0;0;1;1;0;1;0;1;1;0;1;1;1;0}) —> returns the summation of odd numbers.
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.

Press F5 to execute the code.

It will output the total amount of even numbers.

Download Practice Workbook

Count Odd and Even Numbers.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

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

1.1 Counting Odd Numbers

1.2 Counting Even Numbers

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

2.1 Count Odd Numbers

2.2 Count Even Numbers

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

Leave a Reply Cancel reply