Counts all cell containing even numbers in the range

[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?

 

[Biff]

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

 

[Thomas]

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

 

[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



.

 

[Aladin Akyurek]

=SUMPRODUCT(--(ISNUMBER(1/A1:Z1)),--(MOD(A1:Z1,2)=0))

 

[Ron Rosenfeld]

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?

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

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






--ron