Count cells that contain odd numbers
=SUMPRODUCT(--(MOD(rng,2)=1))
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 E5 is:
=SUMPRODUCT(--(MOD(rng,2)=1))
This formula returns 4 since there are 4 odd numbers in the range B5:B10 (which is named range, "rng" in the formula).
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(rng,2)=1
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 B5:B10, the result is an array of TRUE / FALSE values:
{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE}
After we coerce the TRUE/FALSE values to numbers using the double negative, we have:
{0;1;1;1;0;1}
SUMPRODUCT then simply sums these numbers and returns 4.
