count Unqiue values based on a cell value

Hi I would like some help with a little problem I have. I have a worksheet that contains a list of account handlers as well as a list of clients

	Column C - Account Handler	Column I - Client
1	Bekki	Fred Bloggs
2	Bekki	Mr Wiggle
3	Bekki	Mr Wiggle
4	Joe	Mr Wiggle

On another worksheet I have a list of those Account Handlers. What I want to have is a formula that counts the unique clients for each Account Handler depending on the account name in a cell.

so for example Bekki has 2 unique Clients and Joe has 1

Hope that makes sense as I am having real problems. Many thanks in advance.

There may be a better way to do this, but here's what i came up with...

Add a helper column with the formula:

Formula:

Please Login or Register  to view this content.

Then, on your worksheet you'll sum that column:

Formula:

Please Login or Register  to view this content.

Obviously you'll have to tweak the columns / cell values to match your actual data set. See the attachment for clarity.

Sorry tried that. I can't use a helper column as the client may have two or more income lines spread over multiple account handlers leading in a faction count.

Do you have a master list of all the clients?

This is even uglier, but might get you goin in a different direction at least...

Formula:

Please Login or Register  to view this content.

where i've got my client list in column f and you have to add a new term for each client you want included....... this covers 2 clients...

i've gotta run for the night at least. hope this helps.

Solution found!

=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$7081=B5,MATCH(Sheet1!I$2:I$7081,Sheet1!I$2:I$7081,0)),ROW(Sheet1!I$2:I$7081)-ROW(Sheet1!I$2)+1),1))

interesting... this isn't quite working for me... my second if statement returns false for everyone but the top account handler, frequency returns every number from 1 to 7080 to which my first if statement returns false for everything except for 1...

my result shows Bekki has 1 client and everyone else is #Value!

Maybe excel 2010 handles this a little differently?

Glad you got your needed result. Any chance you wouldn't mind uploading a sample workbook so i can see the evaluation?

Did you do the Ctrl Shift Enter rather than just enter? I cant claim credit for this as a very nice man on another forum did it.

oh also there is another table with the list of the account handers that is referenced. I will post my sample tomorrow for people to use