w3resource

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

Count number of cells that contain even 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

Count no. of cells that contain positive 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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/excel/formulas/count/count-no-of-cells-containing-even-or-odd-numbers.php