Pivot - need to recount based on results
 

Hi!

I have a data source that contains client information with several different
attributes (which results in several rows of data for each client). I
created a pivot table that will allow users to filter and look at the clients
in a variety of different way, using the PAGE area within the layout of the
pivot. What I am looking for is a formula/function within the pivot that
will recount the number of unique clients depending on the filter selected.
Is there a way that I can do this within the pivot as I need to have this
count updated with each change to the filters?

Here is an example, I hope it helps:

City City X
State (All)
Line Line A
Sub Line (All)

Data
Product Industry Clients Sum of Clients
Product 1 Industry A Client A 8 would expect to see 1 client count
Industry A Total 8 would expect to see 1 client count
Industry B Client B 3 would expect to see 1 client count
Client C 3 would expect to see 1 client count
Client D 1 would expect to see 1 client count
Industry B Total 7 would expect to see 3 client count

A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivot table.

There's an example he

http://www.contextures.com/xlPivot07.html#Unique


Kreed wrote:
Hi!

I have a data source that contains client information with several different
attributes (which results in several rows of data for each client). I
created a pivot table that will allow users to filter and look at the clients
in a variety of different way, using the PAGE area within the layout of the
pivot. What I am looking for is a formula/function within the pivot that
will recount the number of unique clients depending on the filter selected.
Is there a way that I can do this within the pivot as I need to have this
count updated with each change to the filters?

Here is an example, I hope it helps:

City City X
State (All)
Line Line A
Sub Line (All)

Data
Product Industry Clients Sum of Clients
Product 1 Industry A Client A 8 would expect to see 1 client count
Industry A Total 8 would expect to see 1 client count
Industry B Client B 3 would expect to see 1 client count
Client C 3 would expect to see 1 client count
Client D 1 would expect to see 1 client count
Industry B Total 7 would expect to see 3 client count



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Pivot - need to recount based on results
 

That is pretty much what I thought...thanks for the information, I did get it
to work.!

"Debra Dalgleish" wrote:

A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivot table.

There's an example he

http://www.contextures.com/xlPivot07.html#Unique


Kreed wrote:
Hi!

I have a data source that contains client information with several different
attributes (which results in several rows of data for each client). I
created a pivot table that will allow users to filter and look at the clients
in a variety of different way, using the PAGE area within the layout of the
pivot. What I am looking for is a formula/function within the pivot that
will recount the number of unique clients depending on the filter selected.
Is there a way that I can do this within the pivot as I need to have this
count updated with each change to the filters?

Here is an example, I hope it helps:

City City X
State (All)
Line Line A
Sub Line (All)

Data
Product Industry Clients Sum of Clients
Product 1 Industry A Client A 8 would expect to see 1 client count
Industry A Total 8 would expect to see 1 client count
Industry B Client B 3 would expect to see 1 client count
Client C 3 would expect to see 1 client count
Client D 1 would expect to see 1 client count
Industry B Total 7 would expect to see 3 client count



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html