I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have any
suggestions?

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?

Thanks for the help. However it still counted zeros therefore we added a
minus one at the end to eliminate the zeros contribution. Thanks again.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?

That is odd, it doesn't for me.

COUNTIF(A1:A20,A1:A20) will count the zeros, but the (A1:A20<0) excludes
them and so you end up dividing FALSE by a number, which is 0 not 1.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
Thanks for the help. However it still counted zeros therefore we added a
minus one at the end to eliminate the zeros contribution. Thanks again.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in
message
...
I am trying to count dates in a column that do not repeat. For
example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you
have
any
suggestions?

Can I convert this one easily to have it show the number of times a specific
day DOES repeat? Endresult shoud look like this:
Column1.....Column2
6/17/2006...2
5/24/2006...1
4/6/2006.....5
etc.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?

=countif(a1:a10,b1)
With the unique date in B1.

You may want to look at creating a pivottable for this.

R-P wrote:

Can I convert this one easily to have it show the number of times a specific
day DOES repeat? Endresult shoud look like this:
Column1.....Column2
6/17/2006...2
5/24/2006...1
4/6/2006.....5
etc.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?




--

Dave Peterson

Thanks Dave, but the unique date can be any date from last year, and possibly
even longer ago...
But keep the ideas coming...

"Dave Peterson" wrote:

=countif(a1:a10,b1)
With the unique date in B1.

You may want to look at creating a pivottable for this.

R-P wrote:

Can I convert this one easily to have it show the number of times a specific
day DOES repeat? Endresult shoud look like this:
Column1.....Column2
6/17/2006...2
5/24/2006...1
4/6/2006.....5
etc.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?




--

Dave Peterson

=countif() will work with larger ranges and any valid dates.



R-P wrote:

Thanks Dave, but the unique date can be any date from last year, and possibly
even longer ago...
But keep the ideas coming...

"Dave Peterson" wrote:

=countif(a1:a10,b1)
With the unique date in B1.

You may want to look at creating a pivottable for this.

R-P wrote:

Can I convert this one easily to have it show the number of times a specific
day DOES repeat? Endresult shoud look like this:
Column1.....Column2
6/17/2006...2
5/24/2006...1
4/6/2006.....5
etc.

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A200<0)/COUNTIF(A1:A200,A1:A200))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SouthCarolina" wrote in message
...
I am trying to count dates in a column that do not repeat. For example

3/12/2006
3/14/2006
4/1/2006
4/1/2006
4/12/2006
0
0

I want the count function to return "4" as the number of entries.
Basically, I want it to exclude zeros and repeating dates. Do you have
any
suggestions?




--

Dave Peterson


--

Dave Peterson