Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805

Hello,

To count even numbers:
=SUMPRODUCT(--(MOD($B$3:$H$11,2)=0))

To count odd ones;
=SUMPRODUCT(--(MOD($B$3:$H$11,2)=1))

If both results do not add up to 63 then you have fractional numbers in
your area (1.1 or similar)...

HTH,
Bernd

Hi,

for odd numbers:

=SUMPRODUCT((MOD($B$3:$H$11,2)=1)*1)

and for even numbers (including 0):

=SUMPRODUCT((MOD($B$3:$H$11,2)=0)*1)

Regards
Ingolf


abc99 schrieb:

Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805

On Mon, 3 Jul 2006 06:48:44 -0500, abc99 wrote:

Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^

Create separate range of cells, with ones for odd and zeros for even, then
use COUNTIF formula.

Or, write a VBA function, name it CountOdd and use it in a formula.

Public Function CountOdd(r as Excel Range) As Long
dim retval as long
For Each c in r.Cells
if c.value mod 2 = 1 then retval = retval+1
Nect c
CountOdd=retval
End Function

Do similar thing for even values...

--
PL

Wow it works it works!!!! Thank you all so much Thank you all.
/bow /bow wow looks like excel is gonna be fun learning from all of
you. Thank you:) :) :)


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805