How to Count Cells That Contain Odd Numbers in Excel

Want to learn more about how to count cells that contain odd numbers using an Excel formula? This post will give you an overview of how to use the SUMPRODUCT formula to count those cells that contain odd numbers.

Syntax(Generic Formula) to Count Cells Using Odd Numbers


The MOD function in a formula returns the remainder after performing a division operation. The divisor in this division formula is 2, so the MOD function will generate a remainder of one(1) for an odd value and will return a remainder of zero(0) for any even numbers.

The range of cells which you want to count. The double negative {–} sign converts this array into a series of 1 and 0 values.

Example and how the SUMPRODUCT works

The SUMPRODUCT function is used to multiply the corresponding components in two given arrays and returns the sum of those products. It returns the remainder when the number is divided by the divisor. The result has the same sign as divisor.

Figure 1: Example of count all cells that contain odd numbers

To count all cells that contain odd numbers, you can use the SUMPRODUCT function. In the example selected cell shown, the formula in F6 is:



In the above example, the MOD(TestData,2)=0 function check whether the cell within the range containing the numbers are divisible by 2 or not.

The result stores an array like the following:

If you have a long list of data in Excel, using this method will allow you to easily count the number of cells which contain odd numbers.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment