Count cells that contain odd numbers

In this example, the goal is to count odd numbers in the range B5:B15, which is named data. This can be done with the SUMPRODUCT function together with the ISODD function. Instead of ISODD, the MOD function can also be used. Both approaches are explained below.

SUMPRODUCT with ISODD

The SUMPRODUCT function works directly with arrays. One thing you can do quite easily with SUMPRODUCT is perform a logical test on a range, then count the results. In this case, we want to count odd numbers, and the simplest way to do that is with the ISODD function. The ISODD function returns TRUE when given an odd number and FALSE when given an even number:

=ISODD(9)// returns FALSE
=ISODD(4)// returns TRUE

In the worksheet shown, the formula in E6 is:

=SUMPRODUCT(--ISODD(+data))

Working from the inside out, the logical test is based on the ISODD function:

ISODD(+data)

In this case, because data (B5:B15) contains 11 values, the ISODD function returns 11 results in an array like this*:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

* There are a small number of functions in Excel that will not spill when given a range of values. ISODD is one of these functions. The + operator is an easy way to make ISODD return all results.

In this array, the TRUE values correspond to cells that contain odd numbers, and the FALSE values represent cells that contain even numbers. To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--):

--{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

The resulting array inside the SUMPRODUCT function looks like this:

=SUMPRODUCT({0;0;1;0;0;0;0;1;1;0;1}) // returns 4

With a single array to process, SUMPRODUCT sums the array and returns 4 as the result.

SUMPRODUCT with MOD

Another way to solve this problem is to use the MOD function like this:

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

Here the logical test for odd numbers looks is this expression:

MOD(data,2)=1

MOD returns a remainder after division. Since, the divisor is 2, MOD will return a remainder of 1 for any odd integer, and a remainder of zero for an even integer. We therefore test for 1. Since there are 11 values in data (B5:B15), the result is an array of 11 TRUE / FALSE values:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--):

--{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

The resulting array inside the SUMPRODUCT function looks like this:

=SUMPRODUCT({0;0;1;0;0;0;0;1;1;0;1}) // returns 4

With a single array to process, SUMPRODUCT sums the array and returns 4 as the result.

Counting even numbers

As you might guess, you can count even numbers in a range by making small adjustments to the formulas above. To adapt the ISODD version of the formula to count even numbers, simply replace the ISODD function with the ISEVEN function like this:

=SUMPRODUCT(--ISEVEN(+data)) // returns 7

To adapt the MOD function version of the formula, adjust the logic to check for zero instead of 1:

=SUMPRODUCT(--(MOD(data,2)=0)) // returns 7

The behavior of these formulas is the same as described previously, and both formulas return 7, since the range B5:B15 contains 7 even numbers.