Counting unique values with criteria
 

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

Counting unique values with criteria
 

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

"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

Counting unique values with criteria
 

Try something like

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

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=534492

Counting unique values with criteria
 

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

"Duke Carey" wrote:

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

"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

Counting unique values with criteria
 

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.

"Kevin McCartney" wrote:

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

"Duke Carey" wrote:

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

"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

Counting unique values with criteria
 

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

"Dav" wrote:


Try something like

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

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=534492

Counting unique values with criteria
 

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

"Duke Carey" wrote:

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.

"Kevin McCartney" wrote:

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

"Duke Carey" wrote:

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

"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

Counting unique values with criteria
 

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

Counting unique values with criteria
 

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

Counting unique values with criteria
 

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

Counting unique values with criteria
 

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