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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics