Count cells that contain odd numbers with SUMPRODUCT
In this article, I’ll explain how to count cells that contain odd numbers in Excel. This example uses two functions: SUMPRODUCT and MOD.
Before we begin, I suggest that you download the exercise file.
The syntax
If you are familiar with SUMPRODUCT, then the syntax for this formula is simple:
=SUMPRODUCT(--(MOD(range, 2)=1))
where range is the range of numbers to check against your criteria
How the MOD function works
The MOD function returns the remainder after a number has been divided and takes two parameters: number and divisor.
By definition, an odd number is one that, after being divided by 2, returns a remainder of 1. So, for MOD, we will supply the range of cells where we want to count all odd values as the number parameter and 2 for the divisor.
Step-by-step SUMPRODUCT formula
In the exercise file, I have prepared a range of 7 numbers which are stored in cells D3:D9. The formula I’ve used is shown below:
=SUMPRODUCT(--(MOD(D3:D9, 2)=1))
To write the formula pick any blank cell and:
- Type =SUMPRODUCT(
- Followed by — to coerce the TRUE/FALSE values to numbers.
- Open a parenthesis that will hold your criterion.
- Then type MOD(
- Select or type the range to be used as your
numberparameter, followed by a comma. In my example, it’sD3:D9. - Insert 2 as the divisor, and ) to close the MOD function.
- Then, type =1 to make sure your formula is checking for odd numbers.
- Finally, type )) and then press Enter to complete the SUMPRODUCT formula.
The formula will return 4, because there are 4 odd numbers in the range D3:D9.
Remember that this formula will not work if you don’t coerce the TRUE/FALSE values to numbers using the double negative.
The formula explained
The nice part about this formula is that the SUMPRODUCT function works directly with arrays. This means you don’t need to combine multiple functions and convert your formula to an array using the CTRL + SHIFT + ENTER keyboard combination.
It also means that you can perform a test directly on an array using one or more criteria and count the results.
Since we are looking to count all the odd numbers from our range, we create a condition using the MOD function:
MOD(D3:D9, 2)=1
Because our divisor is 2, MOD will return either a remainder of 1 (for odd numbers) or a remainder of zero (for even numbers).
Then, SUMPRODUCT will run the test against each of the 7 values from cells D3:D9 and return an array of TRUE and FALSE values:
{FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE}
After all the TRUE/FALSE values are coerced (using the — double negative), we have:
{0, 1, 0, 0, 1, 1, 1}
In the final step, SUMPRODUCT will sum all the values from the coerced array and return 4.
What to do next?
If you still have questions about how to count cells that contain odd numbers, please post a comment. I reply to all comments that I receive.