MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count unique records


Posted by Alice on January 23, 2002 7:17 AM

Is there a way that I can count all of the UNIQUE records in column B?


Posted by Manu on January 23, 2002 7:44 AM

I am aware of accomplishing this via MS Access, let me know if you'd like to and I shall certainly try and assist

Posted by Mike E B on January 23, 2002 7:47 AM

Assuming that your values in column B start at B1, try the following.

In C1 type 1

In c2 type =IF(ISERROR(MATCH(B3,$B$2:B2,0)),C2+1,C2) and then fill this formula down to the same row as the last value in column B

The number in this last row in column C is then the number of Unique values you have.

-M


Posted by Mark W. on January 23, 2002 7:58 AM

If you're counting unique numeric values consider
using...

=SUM((FREQUENCY(B:B,B:B)>0)+0)

Posted by Steve Hartman on January 23, 2002 8:10 AM

What kind of records are you looking at? Are they muneric? mixed alpha & numeric? any blank records?

Posted by Alice on January 23, 2002 1:06 PM

Alpha - no blanks

Posted by Juan Pablo G. on January 23, 2002 1:42 PM

Re: Alpha - no blanks

Try with this Array Formula. Assuming data in A1:A25

=SUM(IF(LEN(A1:A25),1/COUNTIF($A$1:$A$25,$A$1:$A$25)))

Juan Pablo G.