software

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)gt;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
KMWhy not use Datagt;Filtergt;Advanced Filter to extract the unique combinations of
Cs and Employees, then build your Pivot table off the unique records?

quot;Kevin McCartneyquot; wrote:

gt; Hi TWIMC
gt;
gt; OK, I have the following test data in an Excel workbook and I want to be
gt; able in a pivot table to count the number of clients for each employee.
gt;
gt; A B C
gt; C1 20 EMPLOYEE1
gt; C1 22 EMPLOYEE1
gt; C2 4 EMPLOYEE1
gt; C3 14 EMPLOYEE1
gt; C3 11 EMPLOYEE1
gt; C4 7 EMPLOYEE1
gt; C5 9 EMPLOYEE1
gt; C1 4 EMPLOYEE2
gt; C1 2 EMPLOYEE2
gt; C2 10 EMPLOYEE2
gt; C3 8 EMPLOYEE2
gt; C3 23 EMPLOYEE2
gt; C4 15 EMPLOYEE2
gt; C5 15 EMPLOYEE2
gt;
gt; Currently the pivot table adds up the each row thus giving a total number of
gt; 7 clients for employee 1 but I want to see 5.
gt;
gt; Now I believe I'll need to create a new column to be included in the pivot
gt; table which I can then sum rather than count and I've found on numerous
gt; posting here and on other internet site the following formula,
gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100))),
gt; but I need to adapt this formula to only look at the range for each employee,
gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; how. so any help would be much appreciated.
gt;
gt; TIA
gt; KM
gt;


Try something like

=SUMPRODUCT(((A1:A8lt;gt;quot;quot;)/COUNTIF(A1:A8,A1:A8amp;quot;quot;))*(C1:C8=quot;employee1quot;))

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=534492Unfortunately 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

quot;Duke Careyquot; wrote:

gt; Why not use Datagt;Filtergt;Advanced Filter to extract the unique combinations of
gt; Cs and Employees, then build your Pivot table off the unique records?
gt;
gt; quot;Kevin McCartneyquot; wrote:
gt;
gt; gt; Hi TWIMC
gt; gt;
gt; gt; OK, I have the following test data in an Excel workbook and I want to be
gt; gt; able in a pivot table to count the number of clients for each employee.
gt; gt;
gt; gt; A B C
gt; gt; C1 20 EMPLOYEE1
gt; gt; C1 22 EMPLOYEE1
gt; gt; C2 4 EMPLOYEE1
gt; gt; C3 14 EMPLOYEE1
gt; gt; C3 11 EMPLOYEE1
gt; gt; C4 7 EMPLOYEE1
gt; gt; C5 9 EMPLOYEE1
gt; gt; C1 4 EMPLOYEE2
gt; gt; C1 2 EMPLOYEE2
gt; gt; C2 10 EMPLOYEE2
gt; gt; C3 8 EMPLOYEE2
gt; gt; C3 23 EMPLOYEE2
gt; gt; C4 15 EMPLOYEE2
gt; gt; C5 15 EMPLOYEE2
gt; gt;
gt; gt; Currently the pivot table adds up the each row thus giving a total number of
gt; gt; 7 clients for employee 1 but I want to see 5.
gt; gt;
gt; gt; Now I believe I'll need to create a new column to be included in the pivot
gt; gt; table which I can then sum rather than count and I've found on numerous
gt; gt; posting here and on other internet site the following formula,
gt; gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100))),
gt; gt; but I need to adapt this formula to only look at the range for each employee,
gt; gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; gt; how. so any help would be much appreciated.
gt; gt;
gt; gt; TIA
gt; gt; KM
gt; gt;

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.

quot;Kevin McCartneyquot; wrote:

gt; Unfortunately the records are unique in that I would expect to see a Cs total
gt; for employee1 of 5 and B total summation of 87, if a use the Advance Filter
gt; is removes two of the records which is not want I want.
gt;
gt; Thanks any way
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Why not use Datagt;Filtergt;Advanced Filter to extract the unique combinations of
gt; gt; Cs and Employees, then build your Pivot table off the unique records?
gt; gt;
gt; gt; quot;Kevin McCartneyquot; wrote:
gt; gt;
gt; gt; gt; Hi TWIMC
gt; gt; gt;
gt; gt; gt; OK, I have the following test data in an Excel workbook and I want to be
gt; gt; gt; able in a pivot table to count the number of clients for each employee.
gt; gt; gt;
gt; gt; gt; A B C
gt; gt; gt; C1 20 EMPLOYEE1
gt; gt; gt; C1 22 EMPLOYEE1
gt; gt; gt; C2 4 EMPLOYEE1
gt; gt; gt; C3 14 EMPLOYEE1
gt; gt; gt; C3 11 EMPLOYEE1
gt; gt; gt; C4 7 EMPLOYEE1
gt; gt; gt; C5 9 EMPLOYEE1
gt; gt; gt; C1 4 EMPLOYEE2
gt; gt; gt; C1 2 EMPLOYEE2
gt; gt; gt; C2 10 EMPLOYEE2
gt; gt; gt; C3 8 EMPLOYEE2
gt; gt; gt; C3 23 EMPLOYEE2
gt; gt; gt; C4 15 EMPLOYEE2
gt; gt; gt; C5 15 EMPLOYEE2
gt; gt; gt;
gt; gt; gt; Currently the pivot table adds up the each row thus giving a total number of
gt; gt; gt; 7 clients for employee 1 but I want to see 5.
gt; gt; gt;
gt; gt; gt; Now I believe I'll need to create a new column to be included in the pivot
gt; gt; gt; table which I can then sum rather than count and I've found on numerous
gt; gt; gt; posting here and on other internet site the following formula,
gt; gt; gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100))),
gt; gt; gt; but I need to adapt this formula to only look at the range for each employee,
gt; gt; gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; gt; gt; how. so any help would be much appreciated.
gt; gt; gt;
gt; gt; gt; TIA
gt; gt; gt; KM
gt; gt; gt;

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

quot;Davquot; wrote:

gt;
gt; Try something like
gt;
gt; =SUMPRODUCT(((A1:A8lt;gt;quot;quot;)/COUNTIF(A1:A8,A1:A8amp;quot;quot;))*(C1:C8=quot;employee1quot;))
gt;
gt; Regards
gt;
gt; Dav
gt;
gt;
gt; --
gt; Dav
gt; ------------------------------------------------------------------------
gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; View this thread: www.excelforum.com/showthread...hreadid=534492
gt;
gt;

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

quot;Duke Careyquot; wrote:

gt; But you can extract the unique values to a new range, leaving the original
gt; data intact. Then use the new range as the source for your pivot table.
gt;
gt; quot;Kevin McCartneyquot; wrote:
gt;
gt; gt; Unfortunately the records are unique in that I would expect to see a Cs total
gt; gt; for employee1 of 5 and B total summation of 87, if a use the Advance Filter
gt; gt; is removes two of the records which is not want I want.
gt; gt;
gt; gt; Thanks any way
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; Why not use Datagt;Filtergt;Advanced Filter to extract the unique combinations of
gt; gt; gt; Cs and Employees, then build your Pivot table off the unique records?
gt; gt; gt;
gt; gt; gt; quot;Kevin McCartneyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi TWIMC
gt; gt; gt; gt;
gt; gt; gt; gt; OK, I have the following test data in an Excel workbook and I want to be
gt; gt; gt; gt; able in a pivot table to count the number of clients for each employee.
gt; gt; gt; gt;
gt; gt; gt; gt; A B C
gt; gt; gt; gt; C1 20 EMPLOYEE1
gt; gt; gt; gt; C1 22 EMPLOYEE1
gt; gt; gt; gt; C2 4 EMPLOYEE1
gt; gt; gt; gt; C3 14 EMPLOYEE1
gt; gt; gt; gt; C3 11 EMPLOYEE1
gt; gt; gt; gt; C4 7 EMPLOYEE1
gt; gt; gt; gt; C5 9 EMPLOYEE1
gt; gt; gt; gt; C1 4 EMPLOYEE2
gt; gt; gt; gt; C1 2 EMPLOYEE2
gt; gt; gt; gt; C2 10 EMPLOYEE2
gt; gt; gt; gt; C3 8 EMPLOYEE2
gt; gt; gt; gt; C3 23 EMPLOYEE2
gt; gt; gt; gt; C4 15 EMPLOYEE2
gt; gt; gt; gt; C5 15 EMPLOYEE2
gt; gt; gt; gt;
gt; gt; gt; gt; Currently the pivot table adds up the each row thus giving a total number of
gt; gt; gt; gt; 7 clients for employee 1 but I want to see 5.
gt; gt; gt; gt;
gt; gt; gt; gt; Now I believe I'll need to create a new column to be included in the pivot
gt; gt; gt; gt; table which I can then sum rather than count and I've found on numerous
gt; gt; gt; gt; posting here and on other internet site the following formula,
gt; gt; gt; gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100))),
gt; gt; gt; gt; but I need to adapt this formula to only look at the range for each employee,
gt; gt; gt; gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; gt; gt; gt; how. so any help would be much appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt; TIA
gt; gt; gt; gt; KM
gt; gt; gt; gt;

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 gt;,
Kevin McCartney gt; wrote:

gt; Hi TWIMC
gt;
gt; OK, I have the following test data in an Excel workbook and I want to be
gt; able in a pivot table to count the number of clients for each employee.
gt;
gt; A B C
gt; C1 20 EMPLOYEE1
gt; C1 22 EMPLOYEE1
gt; C2 4 EMPLOYEE1
gt; C3 14 EMPLOYEE1
gt; C3 11 EMPLOYEE1
gt; C4 7 EMPLOYEE1
gt; C5 9 EMPLOYEE1
gt; C1 4 EMPLOYEE2
gt; C1 2 EMPLOYEE2
gt; C2 10 EMPLOYEE2
gt; C3 8 EMPLOYEE2
gt; C3 23 EMPLOYEE2
gt; C4 15 EMPLOYEE2
gt; C5 15 EMPLOYEE2
gt;
gt; Currently the pivot table adds up the each row thus giving a total number of
gt; 7 clients for employee 1 but I want to see 5.
gt;
gt; Now I believe I'll need to create a new column to be included in the pivot
gt; table which I can then sum rather than count and I've found on numerous
gt; posting here and on other internet site the following formula,
gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$
gt; 100))),
gt; but I need to adapt this formula to only look at the range for each employee,
gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; how. so any help would be much appreciated.
gt;
gt; TIA
gt; KM

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

quot;Domenicquot; wrote:

gt; Try the following formula, which needs to be confirmed with
gt; CONTROL SHIFT ENTER...
gt;
gt; =COUNT(1/FREQUENCY(IF(C$2:C$100=E2,MATCH(A$2:A$100,A$2:A$10 0,0)),ROW(A$2:
gt; A$100)-ROW(A$2) 1))
gt;
gt; ....where E2 contains the employee of interest.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; Kevin McCartney gt; wrote:
gt;
gt; gt; Hi TWIMC
gt; gt;
gt; gt; OK, I have the following test data in an Excel workbook and I want to be
gt; gt; able in a pivot table to count the number of clients for each employee.
gt; gt;
gt; gt; A B C
gt; gt; C1 20 EMPLOYEE1
gt; gt; C1 22 EMPLOYEE1
gt; gt; C2 4 EMPLOYEE1
gt; gt; C3 14 EMPLOYEE1
gt; gt; C3 11 EMPLOYEE1
gt; gt; C4 7 EMPLOYEE1
gt; gt; C5 9 EMPLOYEE1
gt; gt; C1 4 EMPLOYEE2
gt; gt; C1 2 EMPLOYEE2
gt; gt; C2 10 EMPLOYEE2
gt; gt; C3 8 EMPLOYEE2
gt; gt; C3 23 EMPLOYEE2
gt; gt; C4 15 EMPLOYEE2
gt; gt; C5 15 EMPLOYEE2
gt; gt;
gt; gt; Currently the pivot table adds up the each row thus giving a total number of
gt; gt; 7 clients for employee 1 but I want to see 5.
gt; gt;
gt; gt; Now I believe I'll need to create a new column to be included in the pivot
gt; gt; table which I can then sum rather than count and I've found on numerous
gt; gt; posting here and on other internet site the following formula,
gt; gt; =SUM(IF(LEN(Sheet1!$A$2:$A$100)gt;0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$
gt; gt; 100))),
gt; gt; but I need to adapt this formula to only look at the range for each employee,
gt; gt; so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or
gt; gt; how. so any help would be much appreciated.
gt; gt;
gt; gt; TIA
gt; gt; KM
gt;

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