Counting Unique Occurances
Posted by DeryckB on November 14, 2001 6:29 AM
I have a database of approx. 1000 records and need to count the number of times a reference against each record occurs using criteria in an adjacent column.
For example. Col.Code1 contains a reference 500012. Col.Code2 contains multiple references, many of which are duplicated, for e.g. 500500, 500501 etc. but are on the same row as reference to 500012.
There may be 15 entries for 500500, 12 for 500501. What I need to ascertain is that there is a count of 2 against 500012 and not 27.
Returning the count of unique references against the criteria specified is taxing my overwhelmed brain!
Posted by IML on November 14, 2001 7:37 AM
Have you tried using countif?
I'm not sure I fully understand, but lets say you want to see how many time A1 appears in a list B1:B20. The formula would be
=COUNTIF($B$1:$B$20,A1)
good luck
Posted by DeryckB on November 15, 2001 3:05 AM
Countif does not work in this situation. For example
Code1 Code2
500012 500500
500012 500500
500012 500501
500013 500510
500012 500500
500013 500510
500012 500501
500012 500500
Countif would return either 4 or 2 depending on the criteria used.
What is required is to count the number of unique numbers in Code2 associated with the reference in Code1.
The answer against 500012 (in Code1) would be 2 and for 500013 the answer would be 1
