Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Sometimes, we might face the urge to count odd and even numbers in Microsoft Excel to understand the entries. In this article, I will explain 3 smart ways to count odd and even numbers in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Smart Ways to Count Odd and Even Numbers in Excel

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. In a scenario, we have a student’s information in the Registration Number and Student Name columns and we want to Count Odd and Even numbers in the dataset. Let’s dive into the details.

Dataset to Count Odd and Even Numbers in Excel


1. Combine SUM and MOD Functions to Count Odd and Even Numbers

In order to count odd and even numbers, we can use a formula that is a combination of SUM and MOD functions. Let’s follow the following procedures for details.

1.1 Counting Odd Numbers

Steps:

  • Apply the following formula in your preferred cell for the count of odd numbers.

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

Combine SUM and MOD Functions to Count Odd and Even Numbers

⧪ 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:

  • Insert the following formula in your desired location to have the desired output.

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

Combine SUM and MOD Functions to Count Odd and Even Numbers

⧪ 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

Read More: Excel Count Cells with Numbers (5 Simple Ways)


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

The MOD function can also be merged with the SUMPRODUCT function to count odd and even numbers. Follow the steps mentioned below for more details.

2.1 Count Odd Numbers

Steps:

  • First of all, pick a cell and input the following formula in that cell to have the amount of odd numbers.

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

Merge SUMPRODUCT & MOD Functions to Count Odd and Even Numbers

⧪ 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:

  • Insert the following formula to have the amount of even numbers in your desired location.

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

Merge SUMPRODUCT & MOD Functions to Count Odd and Even Numbers

⧪ 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

Read More: How to Count Non Blank Cells with Condition in Excel (6 Methods)


Similar Readings


3. Apply VBA Macro to Count Odd and Even Numbers

The smartest way to have the total amount of odd and even numbers is to apply the VBA Macro. It’s actually not that hard. You will know it after reading the following section.

Steps:

  • Press  ALT +F11  altogether. Microsoft Visual Basic will open.
  •  From the Toolbar click on Insert & Select Module.

Insert Module

For Counting Odd Numbers

  • Write the below 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

  • Next, press  F5  or click on the Run button to execute the code.

Apply VBA Code

  • You will have the total amount of odd numbers in your selected cell.

Apply VBA Macro to Count Odd and Even Numbers


For Counting Even Numbers

  • Input the below 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.

Apply VBA Code

  • Thus, we will have the total amount of even numbers.

Apply VBA Macro to Count Odd and Even Numbers

Read More: How to Count If Cell Contains Number in Excel (5 Smart Ways)


Conclusion

At the end of this article, I like to add that I have tried to explain 3 smart ways to count odd and even numbers in Excel. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo