Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create a pivot table based on multiple sheets | Excel Discussion (Misc queries) | |||
pivot table based on 4 others (in seperate workbooks)? | Excel Discussion (Misc queries) | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Pivot Table for survey results with set of possible answers in PT's Rows Field? | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |