Hi Domenic,

you are awarded the Gold star, you got it to work, thank you so very much,
your talents are highly appreciated.

Thanks and take care
ciao
KM

"Domenic" wrote:

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=COUNT(1/FREQUENCY(IF(C$2:C$100=E2,MATCH(A$2:A$100,A$2:A$10 0,0)),ROW(A$2:
A$100)-ROW(A$2)+1))

....where E2 contains the employee of interest.

Hope this helps!

In article ,
Kevin McCartney wrote:

Hi TWIMC

OK, I have the following test data in an Excel workbook and I want to be
able in a pivot table to count the number of clients for each employee.

A B C
C1 20 EMPLOYEE1
C1 22 EMPLOYEE1
C2 4 EMPLOYEE1
C3 14 EMPLOYEE1
C3 11 EMPLOYEE1
C4 7 EMPLOYEE1
C5 9 EMPLOYEE1
C1 4 EMPLOYEE2
C1 2 EMPLOYEE2
C2 10 EMPLOYEE2
C3 8 EMPLOYEE2
C3 23 EMPLOYEE2
C4 15 EMPLOYEE2
C5 15 EMPLOYEE2

Currently the pivot table adds up the each row thus giving a total number of
7 clients for employee 1 but I want to see 5.

Now I believe I'll need to create a new column to be included in the pivot
table which I can then sum rather than count and I've found on numerous
posting here and on other internet site the following formula,
=SUM(IF(LEN(Sheet1!$A$2:$A$100)0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$
100))),
but I need to adapt this formula to only look at the range for each employee,
so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
how. so any help would be much appreciated.

TIA
KM