Hassan Shuvo
If you ever need to count the number of odd and even numbers in a data range, Excel has some interesting functions to assist you. In this article, we will show you how to count odd and even numbers in Excel in different ways.
📁 Download Excel File
Download this file for easier understanding.
Learn to Count Odd and Even Numbers in Excel with These 3 Methods
We will show you 3 different methods to count odd and even numbers in Excel using the SUM, SUMPRODUCT, and MOD functions. We will also include a VBA code to make the process much handier. Here, we used the results of a couple of students in a classroom to assist us in this case.
1. Applying SUM and MOD Functions Together
First, in order to count the number of odd or even numbers, we will apply the SUM and MOD functions in conjunction. But we need to learn about the functions, to begin with.
Syntax of SUM function:
SUM(number1, [number2], [number3], …)
The arguments are:
number1 – the first value to add
number2 – the second value to add
number3 – the third value to add
The last two are optional. The provided values’ sum is what the SUM function returns. Any combination of numbers, cell references, ranges, arrays, and constants can be used as these values. SUM can handle 255 different arguments at once.
Syntax of MOD function:
MOD(number, divisor)
The arguments:
number – The number you’re trying to find the remainder for.
divisor – the number you want to divide the input integer by.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell C19 and type this formula.
=SUM(1*(MOD(C6:C17,2)=1))
Then, hit Enter and find the result.
Then, hit Enter and find the result.
- For Even numbers, after selecting C20, type the same formula in the following fashion.
=SUM(1*(MOD(C6:C17,2)=0))
- Henceforth, hitting Enter will reveal the number of even numbers.
🔨 Formula Breakdown
👉 The MOD function determines whether or not the integers in the range C6:C17 can be divided by 2. If it is TRUE, it means that the number in question is an even number. This formula produces an array result that looks like {TRUE; FALSE; TRUE;…} The array is then multiplied by 1 to change these TRUE and FALSE values into 1 and 0 values.
👉 Then, these numbers will be added together before being returned by the SUM function.
Read More: 5 Examples to Count Filled Cells Using VBA in Excel
2. Combining SUMPRODUCT and MOD Functions
You can use a formula based on the SUMPRODUCT function and the MOD function to count the number of cells in the range C6:C17 that contain odd numbers. Let’s learn about the SUMPRODUCT function first.
Syntax of SUMPRODUCT:
SUMPRODUCT(array1, [array2], …)
array1 – the initial range or array to multiply before adding.
array2 – the second array or range to multiply by two before adding. This is optional.
The SUMPRODUCT function in Excel multiplies ranges or arrays and outputs the total of the products.
Now, let’s get to the example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell C19 and type this formula.
=SUMPRODUCT(–(MOD(C6:C17,2)<>0))
- Then, hit Enter and find the result.
- For Even numbers, after selecting C20, type the same formula in the following fashion.
=SUMPRODUCT(–(MOD(C6:C17,2)=0))
- Hence, hitting Enter will reveal the number of even numbers.
🔨 Formula Breakdown
👉 The MOD function determines whether or not the integers in the range C6:C17 can be divided by 2. If it is TRUE, it means that the number in question is an even number. This formula produces an array result that looks like {TRUE; FALSE; TRUE;…} Then, the result would become {1;0;1;…….}, thanks to the double negative operator.
👉 These numbers will be added together before being returned by the SUMPRODUCT function.
Read More: 3 Ways to Count Blank Cells with Condition in Excel
3. Using VBA Code
Finally, we have arrived at the final method for counting odd and even numbers, in which we will employ the VBA script. Before writing VBA code, we enabled the Developer tab in our Ribbon. Find out how to access the Developer tab here.
These instructions will help you write your code when you access the Developer tab.
⬇️⬇️ Steps ⬇️⬇️
- While on the Developer tab, click the Visual Basic button in the Ribbon’s left-hand corner.
- Thus, a window will show up where you will click Insert and subsequently, Module.
- In the module, copy and paste the following formula.
Sub CountingOddEvenNumbers()
Dim WB As Worksheet
Dim datarange As Range
Set WB = Worksheets("VBA")
Set datarange = WB.Range("C6:C17")
For Each x In datarange
cellx = x Mod 2
Odd = Odd + cellx
Next x
WB.Range("C19") = Odd
For Each x In datarange
If x Mod 2 = 0 Then
Even = Even + 1
End If
Next x
WB.Range("C20") = Even
End Sub
- Finally, to apply change press F5 or click on Run and click Run Sub/Userform.
As a result, the amounts of odd and even numbers will be printed in cells C19 and C20.
📄 Important Notes
🖊️ The #DIV/0! error value is returned by the MOD function if the divisor is 0.
🖊️ SUMPRODUCT function returns the total of the products after multiplying arrays.
🖊️ The SUM function accepts up to 255 arguments total in the form of number1, number2, number3, etc.
🖊️ SUM helps sum cells that might contain text values because it automatically disregards empty cells and text values.
📝 Takeaways from This Article
📌 First, we combined SUM and MOD functions to count odd and even numbers.
📌 Next, we used SUMPRODUCT with the MOD function to achieve a similar result.
📌 Lastly, using the VBA script, we demonstrated a quicker approach.
Conclusion
This is the end of our article. We tried our best to cover all sorts of methods to make the process easier. We hope, you can now easily count the number of odd and even numbers in Excel. For more tips and tricks for Excel, visit Excelden.com.
