MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count Odd/Even Numbers in a range


Posted by Robb on September 25, 2001 6:26 AM

I would like to count the number of odd & even numbers in a defined range. How would I do this?


Posted by Aladin Akyurek on September 25, 2001 6:43 AM

Robb --

To count odd numbers, use:

=SUMPRODUCT((MOD(A1:100,2)<>0)+0)

To count even numbers, use:

=SUMPRODUCT((MOD(A1:100,2)=0)+0)

Aladin

Posted by Robb on September 26, 2001 6:38 AM


Thanx Aladin!!

Question, how were you able to figure this out??

Posted by Aladin Akyurek on September 26, 2001 10:10 AM

Gee, Robb, this one is a lot more thougher than the original question.

A Quinean account of how I figured it out would be:

[1] Numbers which are divisible by 2 are even numbers.
[2] Numbers which are not divisible by 2 are uneven (or odd) numbers.
[3] Since N = Quotient * 2 + Remainder (where 2 is a divisor), Remainder must be 0 for even N and greater than zero for odd N.

[1] to [3] flowed in from the school maths.

[4] =MOD(N,x) produces a remainder (from some of the programming languages that I know and Excel).

[5] The function can be used as a Boolean test as in =MOD(N,x)=0 and =MOD(N,x)<>0 (from some of the programming languages that I know and Excel).

[6] If applied to a range, MOD produces a constant array of numbers in an array formula or in a SUMPRODUCT formula. In its Boolean form, it produces a constant array of logical values. (From Excel.)

[7] Coercion [that is, +0] turns a constant array of logical values into one consisting of 1's and/or 0's (from Excel).

[8] SUMPRODUCT sums an array of 1's and 0's just like an array formula with SUM would, producing a count of 1's (from Excel).

Whence the formula.

Aladin


Posted by Robb on September 26, 2001 1:35 PM

Thanx again Aladin, you are simply amazing.

Robb