Counting non-repeating dates

[Guest]

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?

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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]

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)

 

[Guest]

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.

 

[Dave Peterson]

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

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

 

[Guest]

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]

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

 

[Guest]

Yep, get it now.

Thanks

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