=SUMPRODUCT(--(MOD(rng,2)=1))

If you need 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 shown the formula in cell E6 is:

=SUMPRODUCT(--(MOD(rng,2)=1))This formula returns 4 since there are 4 odd numbers in the range B6:B11 (which is named range, “rng” in the formula).

## How this formula works

The SUMPRODUCT function works directly with arrays.

On 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)=1MOD 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:

{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 up and returns 4.