Counts all cell containing even numbers in the range

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

Hi Everyone.

I need some help in counting all cell with even numbers in a range.

I used the below formula for range A1 to Z1,

=SUMPRODUCT(N(MOD(A1:Z1,2)=0))

The above does not work as expected.

1. The problem is if the cell is empty or 0, Excel treat it as an even
number and add to the count. Therfore, if A1 to Z1 contains 0 or empty, the
result will be 26.

2. If even number is added to the cell, the result is still the same - 26
unless odd number are added. Expected result : Total numbers of cell
contains 0 or empty - toal numbers of cell containing odd number.

No problem is encounter when counting odd numbers using the forumla,
=SUMPRODUCT(N(MOD(A1:Z1,2)=1))

Any solutions?
 
Hi Thomas!

Here's an unelegant solution:

=SUM(IF(A1:Z1>0,IF(MOD(A1:Z1,2)=0,1,0)))

Entered as an array - CTRL+SHIFT+ENTER

Biff
 
Thanks.

Hi Thomas!

Here's an unelegant solution:

=SUM(IF(A1:Z1>0,IF(MOD(A1:Z1,2)=0,1,0)))

Entered as an array - CTRL+SHIFT+ENTER

Biff
 
You're welcome

Biff
-----Original Message-----
Thanks.

Hi Thomas!

Here's an unelegant solution:

=SUM(IF(A1:Z1>0,IF(MOD(A1:Z1,2)=0,1,0)))

Entered as an array - CTRL+SHIFT+ENTER

Biff



.
Click to expand...
 
Hi Everyone.

I need some help in counting all cell with even numbers in a range.

I used the below formula for range A1 to Z1,

=SUMPRODUCT(N(MOD(A1:Z1,2)=0))

The above does not work as expected.

1. The problem is if the cell is empty or 0, Excel treat it as an even
number and add to the count. Therfore, if A1 to Z1 contains 0 or empty, the
result will be 26.

2. If even number is added to the cell, the result is still the same - 26
unless odd number are added. Expected result : Total numbers of cell
contains 0 or empty - toal numbers of cell containing odd number.

No problem is encounter when counting odd numbers using the forumla,
=SUMPRODUCT(N(MOD(A1:Z1,2)=1))

Any solutions?
Click to expand...

=SUMPRODUCT((A1:Z1<>0)*(MOD(A1:Z1,2)=0))

should work assuming there are no TEXT entries in the range.






--ron
 
Back
Top