Excel Formulas - Count number of cells containing even or odd numbers

Count number of cells that contain even numbers

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
MOD(number, divisor)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products. Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Explanation

To count the number of cells in a range of cells which containing the even numbers, we can use the SUMPRODUCT function along with the MOD function.

Formula

=SUMPRODUCT(--(MOD(TestData,2)=0))

How this formula works

In the above example the MOD(TestData,2)=0 funtion check whether the cell within the range containing the numbers are devisible by 2 or not.
The result store an array like

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

The double negative (--) sign converts this array into 1 and 0 and the array like -

{1;1;0;;0;0;1;1;}

Therefore SUMPRODUCT function make a sum of the array and returns the result.

Count number of cells that contain odd numbers

Explanation

To count number of positive numbers within a range of cells the COUNTIF function can be used.

Formula

=SUMPRODUCT(--(MOD(TestData,2)<>0))

How this formula works

In the above example the MOD(TestData,2)<>0 funtion check whether the cell within the range containing the numbers are devisible by 2 or not.
The result store an array like

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

The double negative (--) sign converts this array into 1 and 0 and the array like -

{0;0;1;1;1;0;0;}

Therefore SUMPRODUCT function make a sum of the array and returns the result.

Previous: Excel Formulas - Count number of cells that contain positive or negative numbers
Next: Excel Formulas - Count number of cells containing specific text