This tutorial shows how to Count cells that contain odd numbers in Excel using the example below;
To count cells that contain only odd numbers, you can use a formula based on the SUMPRODUCT function together with the MOD function.
In the example, the formula in cell E6 is:
This formula returns 4 since there are 4 odd numbers in the range B6:B11 .
How this formula works
The SUMPRODUCT function works directly with arrays.
One thing you can do quite easily with SUMPRODUCT is perform a test on an array using one or more criteria, then count the results.
In this case, we are running a test for an odd number, which uses the MOD function:
MOD returns a remainder after division. In this case, the divisor is 2, so MOD will return a remainder of 1 for any odd integer, and a remainder of zero for even numbers.
Inside SUMPRODUCT, this test is run on every cell in B6:B11, the result is an array of TRUE / FALSE values:
After we coerce the TRUE/FALSE values to numbers using the double negative, we have:
SUMPRODUCT then simply sums these numbers and returns 4.