Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike Struckman
 
Posts: n/a
Default Using a Pivot Table Calculated Field to get a Unique Count

I am using a Pivot Table to view data from a very large database. The Pivot
Table has the following fields: Customer, Quarter and Sales. There are many
duplicate Customer sales in the database.

I would like to create a Pivot Table that calculates the # of unique
customers in each quarter. If I use the Pivot Table Count function it double
counts all of the duplicate Customer entries. Can I create a Calculated Field
that accomplishes this?

I have tried using this formula that I found in a previous string,
€ś=SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))€ť,
but since I am using it in a Pivot Table calculated field I used the
following format: €ś=SUM(IF(LEN(Customer)0,1/COUNTIF(Customer, Customer)))€ť.
Where €śCustomer€ť is inserted from the Pivot Table field list. I get a €śThe
function contains an error€ť message.

Can you help me understand what I am doing wrong? Thanks!

--
Mike Struckman
  #2   Report Post  
Junior Member
 
Posts: 23
Default

Post an email address and I'll send you what I worked on. This site doesn't allow me to post an Excel file (go figure)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Customize functions in the Data Field PSKelligan Excel Discussion (Misc queries) 2 January 4th 05 07:51 PM
Calculated Field in Pivot Table Edgar Thoemmes Excel Worksheet Functions 0 December 23rd 04 12:59 PM
Setting default pivot table field setting to "sum" Mr. Moose Excel Discussion (Misc queries) 2 December 21st 04 05:43 PM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 06:39 AM
How do I get a pivot table to sum instead of giving me a count? kmm Excel Worksheet Functions 1 November 5th 04 04:44 PM


All times are GMT +1. The time now is 11:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"