pivot tables
Posted by Chris Robinson on September 17, 2001 7:35 AM
I am analysing database of 60,000 customers - each customer s identified by unique id no, product type, size etc,etc.If a customer has more than one product type they will have more than one line in database
I am extracting pivot tables to analyse data and want to count number of customers - I think what the pivot table is doing is counting the total number of customer ID Nos AND not the number of times the number apppears. Hence if a customer has more than one product line he is being counted twice. How can I fix a pivot table to count number of unique nos only ........ help
Posted by brent on September 17, 2001 8:33 AM
This is an extra step, but if it works for you: Supposing in the pivot table, that you have customer id in the row, product in the column and size in the data; you can use the =COUNTIF function for ">0" to tally the column that the id numbers show, in the report.
Posted by Mark W. on September 17, 2001 11:26 AM
Chris, suppose that your 'Customer ID' field is in
column A. You could add a new column, 'Unique ID',
containing the formula, =1/COUNTIF(A:A,A2), in cell
B2 (which is copied down column B to include the last
row of data). Now, include the 'Sum of Unique ID' in
your PivotTable's DATA area and rename it (on the
PivotTable Field dialog) to a more meaningful name
such as 'Unique IDs' or 'Count of Unique ID'.
