Counting unique values with criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
Try something like

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

Regards

Dav
 
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
 
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.
 
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
 
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
 
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!
 
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
 
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
 
Back
Top