Newbie needs help on excel formula

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. ^^

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