A count of unique values

[daglugub37]

Hi All,

I have a worksheet that contains a list of clients along with other info.

The client IDs can have multiple rows in this workbook and I would like to know how many unique clients there are.

any ideas?

 

[000Steve000]

A quote from the Excel Help files:-

Filter for unique records
Select the column or click a cell in the list you want to filter.
On the Data menu, point to Filter, and then click Advanced Filter.
Do one of the following.
To filter the list in place, similar to using AutoFilter, click Filter the list, in-place.
To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog .

Select the Unique records only check box.

 

[GlennUK]

If all you want is a count of the unique values, then use this array formula:

=SUM(1/COUNTIF(A1:A25,A1:A25))

using Ctrl-Shift-Enter instead of Enter. Alter the references to suit your ID ramge.

Cheers, Glenn.

 

[KenWright]

Or

=SUMPRODUCT(1/COUNTIF(A2:A25,A2:A25))

with no need to array enter

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

Yes Ken,

I keep on forgetting about SUMPRODUCT

Cheers, Glenn.