Counting Cells with odd and/or even values

[yungexec]

Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively. I would like to count how many of these cells are odd and place that value in A6. The same for even numbers as well (in A7).

Any help would be appreciated.

[Roger Govier]

Hi

One way
=SUMPRODUCT(--(MOD(A1:A5,2)=1))
For even numbers, change the 1 in the formula to 0

--
Regards

Roger Govier


"yungexec" <yungexec.2biwy6_1153868409.0066@excelforum-nospam.com> wrote
in message news:yungexec.2biwy6_1153868409.0066@excelforum-nospam.com...
>
> Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively.
> I
> would like to count how many of these cells are odd and place that
> value
> in A6. The same for even numbers as well (in A7).
>
> Any help would be appreciated.
>
>
> --
> yungexec
> ------------------------------------------------------------------------
> yungexec's Profile:
> http://www.excelforum.com/member.php...o&userid=22593
> View this thread:
> http://www.excelforum.com/showthread...hreadid=564950
>

[Bob Phillips]

Shame that ISEVEN doesn't work <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:%23Jf2JOEsGHA.5072@TK2MSFTNGP05.phx.gbl...
> Hi
>
> One way
> =SUMPRODUCT(--(MOD(A1:A5,2)=1))
> For even numbers, change the 1 in the formula to 0
>
> --
> Regards
>
> Roger Govier
>
>
> "yungexec" <yungexec.2biwy6_1153868409.0066@excelforum-nospam.com> wrote
> in message news:yungexec.2biwy6_1153868409.0066@excelforum-nospam.com...
> >
> > Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively.
> > I
> > would like to count how many of these cells are odd and place that
> > value
> > in A6. The same for even numbers as well (in A7).
> >
> > Any help would be appreciated.
> >
> >
> > --
> > yungexec
> > ------------------------------------------------------------------------
> > yungexec's Profile:
> > http://www.excelforum.com/member.php...o&userid=22593
> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=564950
> >
>
>

[Roger Govier]

Hi Bob

Yes, I played with that for a while but could not get a solution, so had
to resort to your favourite<g>

--
Regards

Roger Govier


"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:Ot%23V3$HsGHA.4608@TK2MSFTNGP04.phx.gbl...
> Shame that ISEVEN doesn't work <g>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23Jf2JOEsGHA.5072@TK2MSFTNGP05.phx.gbl...
>> Hi
>>
>> One way
>> =SUMPRODUCT(--(MOD(A1:A5,2)=1))
>> For even numbers, change the 1 in the formula to 0
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "yungexec" <yungexec.2biwy6_1153868409.0066@excelforum-nospam.com>
>> wrote
>> in message
>> news:yungexec.2biwy6_1153868409.0066@excelforum-nospam.com...
>> >
>> > Lets say I have range A1:A5 containing values 1,2,3,4,5
>> > respectively.
>> > I
>> > would like to count how many of these cells are odd and place that
>> > value
>> > in A6. The same for even numbers as well (in A7).
>> >
>> > Any help would be appreciated.
>> >
>> >
>> > --
>> > yungexec
>> > ------------------------------------------------------------------------
>> > yungexec's Profile:
>> > http://www.excelforum.com/member.php...o&userid=22593
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=564950
>> >
>>
>>
>
>

[Bob Phillips]

Oh it would still be SUMPRODUCT, but if you could do

=SUMPRODUCT(--(ISEVEN(A1:A5)))

that is so much more self-descriptive than using MOD. Unfortunately, the
ISEVEN function seems incapable of processing an array of values, just as
WEEKNUM cannot. Must be something to do with being an ATP function, I wonder
if it works with 2007 (must try it).

You could use

=SUMPRODUCT(--(EVEN(A1:A5)=A1:A5))

but I am not sure that that is any better than MOD

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:ulKhvCIsGHA.736@TK2MSFTNGP02.phx.gbl...
> Hi Bob
>
> Yes, I played with that for a while but could not get a solution, so had
> to resort to your favourite<g>
>
> --
> Regards
>
> Roger Govier
>
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:Ot%23V3$HsGHA.4608@TK2MSFTNGP04.phx.gbl...
> > Shame that ISEVEN doesn't work <g>
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> > news:%23Jf2JOEsGHA.5072@TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> One way
> >> =SUMPRODUCT(--(MOD(A1:A5,2)=1))
> >> For even numbers, change the 1 in the formula to 0
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "yungexec" <yungexec.2biwy6_1153868409.0066@excelforum-nospam.com>
> >> wrote
> >> in message
> >> news:yungexec.2biwy6_1153868409.0066@excelforum-nospam.com...
> >> >
> >> > Lets say I have range A1:A5 containing values 1,2,3,4,5
> >> > respectively.
> >> > I
> >> > would like to count how many of these cells are odd and place that
> >> > value
> >> > in A6. The same for even numbers as well (in A7).
> >> >
> >> > Any help would be appreciated.
> >> >
> >> >
> >> > --
> >> > yungexec
> >>
> ------------------------------------------------------------------------
> >> > yungexec's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=22593
> >> > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=564950
> >> >
> >>
> >>
> >
> >
>
>