countif

[Guest]

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

 

[Guest]

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

 

[Guest]

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