COUNTIF:series of discontinuous cells

COUNTIF seems to require a continuous range of cells to be inserted in the
formula.
I have a series of discontinuous cells from which I wish to count all those
that have the value 1.
COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
separators does not work either.
Any suggestions?

I tried the formula as a range and it worked. The formula I used was:

=COUNTIF(A1:D5,1)

Hope this helps,
Les



"nsharpe" wrote:

> COUNTIF seems to require a continuous range of cells to be inserted in the
> formula.
> I have a series of discontinuous cells from which I wish to count all those
> that have the value 1.
> COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> separators does not work either.
> Any suggestions?
>

One way:

=(A1=1)+(C3=1)+(D5=1)




In article <[email protected]>,
"nsharpe" <[email protected]> wrote:

> COUNTIF seems to require a continuous range of cells to be inserted in the
> formula.
> I have a series of discontinuous cells from which I wish to count all those
> that have the value 1.
> COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> separators does not work either.
> Any suggestions?

Maybe....
Insert/Name/Define
=A1,C3,D5
as RangeTest for example
and then: =COUNTIF(RangeTest,1)
Does it help?
--
A+

V.


"nsharpe" wrote:

> COUNTIF seems to require a continuous range of cells to be inserted in the
> formula.
> I have a series of discontinuous cells from which I wish to count all those
> that have the value 1.
> COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> separators does not work either.
> Any suggestions?
>

forget it....
--
A+

V.


"Vincnet." wrote:

> Maybe....
> Insert/Name/Define
> =A1,C3,D5
> as RangeTest for example
> and then: =COUNTIF(RangeTest,1)
> Does it help?
> --
> A+
>
> V.
>
>
> "nsharpe" wrote:
>
> > COUNTIF seems to require a continuous range of cells to be inserted in the
> > formula.
> > I have a series of discontinuous cells from which I wish to count all those
> > that have the value 1.
> > COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> > separators does not work either.
> > Any suggestions?
> >

In article <[email protected]>,
"Vincnet." <[email protected]> wrote:

> Maybe....
> Insert/Name/Define
> =A1,C3,D5
> as RangeTest for example
> and then: =COUNTIF(RangeTest,1)
> Does it help?

Did you try it yourself?

Brute force.......

=SUM(COUNTIF(A1,1),COUNTIF(C3,1),COUNTIF(D5,1))

Vaya con Dios,
Chuck, CABGx3



"nsharpe" wrote:

> COUNTIF seems to require a continuous range of cells to be inserted in the
> formula.
> I have a series of discontinuous cells from which I wish to count all those
> that have the value 1.
> COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> separators does not work either.
> Any suggestions?
>

I don't say that it is a good solution but try:

=SUM(COUNTIF(INDIRECT({"A1","C3","D5"}),1))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"nsharpe" <[email protected]> wrote in message
news:[email protected]...
> COUNTIF seems to require a continuous range of cells to be inserted in the
> formula.
> I have a series of discontinuous cells from which I wish to count all
> those
> that have the value 1.
> COUNTIF((A1,C3,D5),1) for example returns #VALUE#. Using colons as
> separators does not work either.
> Any suggestions?
>