Count cells that contain odd numbers

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.