Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Isabelga
 
Posts: n/a
Default Pivot Table- how do I eliminate duplicates in count of name

I have built a pivot table from a table containing a list of people.
My pivot table will count people based on the field Name.
Some people names might have several entries in the table but in this case,
I want the name to be counted a 1.
What is the appropriate set up for this ?
Thankk you for your help.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Jonathan Cooper
 
Posts: n/a
Default Pivot Table- how do I eliminate duplicates in count of name

Insert a helper column into your table, so that it is part of your pivot
table.

This formula may be confusing but I'll try to explain. Assuming your names
are in column A, starting with row 2.

A1 is blank
A2=Eric
A3=George
A3=George

Put this formula into B2. =IF(COUNTIF($A$2:$A2,A2)1,0,1)
and they copy/paste it down.

As you paste it down, the range grows. In cell A3 for example, it counts
how many times it finds 'George' in the list from $A$2:$A3. The answer is 1.
Then the IF kicks in. Since there was only one 'George', which is not 1,
it evaluates to a 1. Then in B3, the countif will result in a 2, which IS
greater than 1, so the answer is zero.

Still confusing, but it will work. Then do a SUM of this column in your
pivot table.



"Isabelga" wrote:

I have built a pivot table from a table containing a list of people.
My pivot table will count people based on the field Name.
Some people names might have several entries in the table but in this case,
I want the name to be counted a 1.
What is the appropriate set up for this ?
Thankk you for your help.

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
How do I use a pivot table to get an average count? Jeff Azano Excel Discussion (Misc queries) 1 January 20th 06 04:10 AM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
count of (blank) pivot table Jtspeakman Excel Discussion (Misc queries) 1 December 12th 05 04:17 PM
count of totals for pivot table SteveM Excel Discussion (Misc queries) 3 September 14th 05 01:56 PM
Pivot Tables, Help? Adam Excel Discussion (Misc queries) 6 March 24th 05 03:35 PM


All times are GMT +1. The time now is 10:55 PM.

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"