Count unique records in list

[ah666]

I am trying to count the unique records in a list. The list changes so I dont
want to filter. I have found a thread on this subject which pointed me to
'Person software counsulting ltd' which had some useful information, however
I want to be able to count by couting in two lists table looks like this :

12/10/04 aa
12/10/04 cc
07/10/04 aa
07/10/04 aa
07/10/04 aa
07/10/04 bb
07/10/04 bb

I have the used the following formula to count the unique records in column
b (where column b is range 1)

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

Which gives me a result of 3

However I want the unique records for 07/10/04 only which should be 2.

If you have any ideas I would much appreciate the help. I dont want to use a
Macro as the sheet is to be used by many users and they need a quick
response, as there is around 10000 records to search through

Additionally I also need to list the unique records i.e. on the 07/10/04 =
aa & bb

 

[Frank Kabel]

Hi
try:
=COUNT(1/FREQUENCY(IF((A1:A20=DATE(2004,10,7))*(B1:B20<>""),MATCH(B1:B2
0,B1:B20,0)),ROW(INDIRECT("1:"&COUNTA(B1:B20)))))

array entered

 

[ah666]

Thanks, however this count all records and just gives me a
total, not sure I quite understand how it is working
either, with respect to indirect and counta

 

[Frank Kabel]

Hi
just change the ranges according to your needs and enter this as array
formula. What is the exact formula you have tried?

 

[ah666]

Hi,

Thanks I've now got it working, missed out a bracket, now
I will try to use the formula to list the unique records.