Counting non-repeating dates

S

SouthCarolina

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?
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

SouthCarolina

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.
 
B

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)
 
R

R-P

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.
 
D

Dave Peterson

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

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

R-P

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