Exceljet

Quick, clean, and to the point

Count cells that contain odd numbers

Excel formula: Count cells that contain odd numbers
Generic formula 
=SUMPRODUCT(--(MOD(rng,2)=1))
Explanation 

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:

=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.

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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.