countif

[FHE]

I would like to use the CountIf statement but applied to a set of separated
cells, rather then a range.

like countif((a1;a3;a5;a7);1).

However I get a 'value' error on this.

Anythoughts?

[Peo Sjoblom]

Not possible, you need to use either multiple countifs, or if your
cells always are a1, a3, a5 and so on (every other cell) you can use

=SUMPRODUCT(--(A1:A150=1);--(MOD(ROW(A1:A150);2)=1))

will count 1 in a1, a3, a5, a7 etc


--

Regards,

Peo Sjoblom


"FHE" <FHE@discussions.microsoft.com> wrote in message
news:14E0C476-B1E7-4B3D-91E0-82865925A1D5@microsoft.com...
> I would like to use the CountIf statement but applied to a set of
separated
> cells, rather then a range.
>
> like countif((a1;a3;a5;a7);1).
>
> However I get a 'value' error on this.
>
> Anythoughts?
>

[Elkar]

Try this:

=SUMPRODUCT(--(MOD(ROW(A1:A10),2)<>0),--(A1:A10=1))

This will count only odd numbered rows in the range A1 thru A10 that have a
value of 1.

HTH
Elkar

"FHE" wrote:

> I would like to use the CountIf statement but applied to a set of separated
> cells, rather then a range.
>
> like countif((a1;a3;a5;a7);1).
>
> However I get a 'value' error on this.
>
> Anythoughts?
>

[FHE]

Thanks a lot to both of you.
Bit of a bummer really, as the cells I need to 'countif' are not needly and
evenly distributed.

Lets see if I can be creative with your solution though...

Thanks again!

Gr Frank

"Peo Sjoblom" wrote:

> Not possible, you need to use either multiple countifs, or if your
> cells always are a1, a3, a5 and so on (every other cell) you can use
>
> =SUMPRODUCT(--(A1:A150=1);--(MOD(ROW(A1:A150);2)=1))
>
> will count 1 in a1, a3, a5, a7 etc
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "FHE" <FHE@discussions.microsoft.com> wrote in message
> news:14E0C476-B1E7-4B3D-91E0-82865925A1D5@microsoft.com...
> > I would like to use the CountIf statement but applied to a set of
> separated
> > cells, rather then a range.
> >
> > like countif((a1;a3;a5;a7);1).
> >
> > However I get a 'value' error on this.
> >
> > Anythoughts?
> >
>
>
>