SUMPRODUCT(($A$1:$A$5000=D2)/COUNTIF($B$1:$B$5000,$B$1:$B$5000&"")*($B$1:$B$500 0<""))

That formula will return an incorrect result if multiple ID's have the same
dates.

...........A..........B..........D
1........x......1/1/09
2........x......1/2/09.......X
3........x......1/3/09
4........y......1/3/09
5........y......1/7/09

--
Biff
Microsoft Excel MVP


"~L" wrote in message
...
Use advanced filter to create a list of unique values from column A in
column
D, with the data starting in row 2, then in E2 enter:

=SUMPRODUCT(($A$1:$A$5000=D2)/COUNTIF($B$1:$B$5000,$B$1:$B$5000&"")*($B$1:$B$500 0<""))

Change the 5000s to some other number to adjust your range as necessary.


"CD27" wrote:

I have 2 columns one with a person ID and the other with various
dates. The file is sorted by person ID. I need to count each unique
date within each ID. So for example if ID 1234 has 3 dates in column
b 8/14, 8/15 and 8/15 again, it would only count 2 and not 3 at the
last occurrence in cell A which is the ID.

100310 5/6/09
136438 5/15/09
136438 5/16/09
136438 5/16/09
279700 5/21/09
296296 5/1/09
316073 5/26/09
329053 5/12/09


Thanks for any help you can provide.

CD