COUNTIF Q
 

I am trying to get the syntax of a COUNTIF function correct and am having a
little problem. I am trying to COUNTIF a number of separate cells within the
formula as opposed to a range. For example

COUNTIF(A1,C1,E1"1") - this produces an error within the formula

Not sure if you can only do a continuous range i.e. COUNTIF(A1:E1,"1"), my
problem is I don't want to count B or D


Thanks

John wrote:
I am trying to get the syntax of a COUNTIF function correct and am having a
little problem. I am trying to COUNTIF a number of separate cells within the
formula as opposed to a range. For example

COUNTIF(A1,C1,E1"1") - this produces an error within the formula

Not sure if you can only do a continuous range i.e. COUNTIF(A1:E1,"1"), my
problem is I don't want to count B or D


Thanks



If the counting is restricted to just 3 cells...

=(A11)+(C11)+(E10)

John

Yes, COUNTIF only takes a contiguous range as argument.

Instead try this formula:

=INDEX(FREQUENCY((A1,C5,C8,A9:A10),1),2)

or, if you name the non-contiguous range e.g. "Rng"

For values 1

=INDEX(FREQUENCY(Rng,1),2)

for values = 1

=INDEX(FREQUENCY(Rng,{0.999999999999999,1}),2)

and for values <=1

=FREQUENCY(Rng,1)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"John" skrev i en meddelelse
...
I am trying to get the syntax of a COUNTIF function correct and am having a
little problem. I am trying to COUNTIF a number of separate cells within
the formula as opposed to a range. For example

COUNTIF(A1,C1,E1"1") - this produces an error within the formula

Not sure if you can only do a continuous range i.e. COUNTIF(A1:E1,"1"),
my problem is I don't want to count B or D


Thanks

Thanks guys

Leo your suggestion fits nicely

"Leo Heuser" wrote in message
...
John

Yes, COUNTIF only takes a contiguous range as argument.

Instead try this formula:

=INDEX(FREQUENCY((A1,C5,C8,A9:A10),1),2)

or, if you name the non-contiguous range e.g. "Rng"

For values 1

=INDEX(FREQUENCY(Rng,1),2)

for values = 1

=INDEX(FREQUENCY(Rng,{0.999999999999999,1}),2)

and for values <=1

=FREQUENCY(Rng,1)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"John" skrev i en meddelelse
...
I am trying to get the syntax of a COUNTIF function correct and am having
a
little problem. I am trying to COUNTIF a number of separate cells within
the formula as opposed to a range. For example

COUNTIF(A1,C1,E1"1") - this produces an error within the formula

Not sure if you can only do a continuous range i.e. COUNTIF(A1:E1,"1"),
my problem is I don't want to count B or D


Thanks

You're welcome, John. Thanks for the feedback :-)

LeoH


"John" skrev i en meddelelse
...
Thanks guys

Leo your suggestion fits nicely