Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count unique items in a pivottable data field.
I have a pivottable and I am trying to setup a data field that will count
unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count unique items in a pivottable data field.
Not the most elegant answer, but I'd probably create my pivot with Customer
in the row field, sales rep in the column field, and sales rep in the data field. The data item will turn into Count of Sales Rep. Let Excel complete the Pivot. Then, in each row, to the right of the Pivot Table, use the counta function. Suppose the first row of data in the pivot table is row 5 and it ends with row totals in column G. Then in H5, =counta(b5:f5) will tell you how many unique reps sold to that customer. Autofill that formula down through the remaining rows. "Jaywestern" wrote: I have a pivottable and I am trying to setup a data field that will count unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count unique items in a pivottable data field.
Debra Dalgleish has some tips at:
http://contextures.com/xlPivot07.html#Unique Jaywestern wrote: I have a pivottable and I am trying to setup a data field that will count unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
count unique with conditions | Excel Worksheet Functions | |||
Pivot Table Data Field Query | Excel Worksheet Functions | |||
Count number of unique items in a column that contains duplicates | Excel Worksheet Functions |