Counting unique values with criteria

[Guest]

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

 

[Guest]

Why not use Data>Filter>Advanced Filter to extract the unique combinations of
Cs and Employees, then build your Pivot table off the unique records?

 

[Dav]

Try something like

=SUMPRODUCT(((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))*(C1:C8="employee1"))

Regards

Dav

 

[Guest]

Unfortunately the records are unique in that I would expect to see a Cs total
for employee1 of 5 and B total summation of 87, if a use the Advance Filter
is removes two of the records which is not want I want.

Thanks any way

 

[Guest]

But you can extract the unique values to a new range, leaving the original
data intact. Then use the new range as the source for your pivot table.

 

[Guest]

Hi Dav,

Unfortunately this doesn't help because the sample data is was just to show
problem that I have, the real data contains several thousand rows so I can't
set the data range individualy for each employee.
Thanks any way

ciao
KM

 

[Guest]

I've tried this but the summation of column B for employee1 does not equal
87, so it does not work, try it, you'll see.

TIA
ciao
KM

 

[Domenic]

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$100,0)),ROW(A$2:
A$100)-ROW(A$2)+1))

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

Hope this helps!

 

[Guest]

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

 

[Mary Katherine]

Kevin,

Where do you insert this formula? Do you insert it into a new column on the
pivot table? Or into the source data?

Thanks!
Mary Katherine

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Modernized formula for source data:
=1/COUNTIFS([Client],[Client],[Empl],[Empl])
Also shown: No formula method:
http://www.mediafire.com/file/2itm5en1hmx/12_31_09.xlsx