Hi all,

I have a sheet with data in Cols A B and C.
A has a unique identifier number, B has client's name and C has person responsible's name:

ie

A B C
169 John Smith Assessor No 1
169 John Smith Assessor No 1
250 Joe Jones Assessor No 1
250 Joe Jones Assessor No 1
250 Joe Jones Assessor No 1
333 Mary White Assessor No 2


Now I can use the =subtotal(3,C2:C7) to get 5 Assessor No 1 and 1 Assessor No 2 and get the answers 5 and 1 respectively. And I can use the =SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")) to get the answer 3.

But what I what to try and use is a variation of the sumproduct function on either Col A or Col B to get the unique entries but with the added problem that I want it to work when I filter the spreadsheet by Col C.

For example if I filter by Assessor No 1 I want the sumproduct formula to tell me I have 2 unique entries in Col B.

Can anyone guide me in the right direction.

TIA

Seamus