Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a pivot table to get an average count? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
count of (blank) pivot table | Excel Discussion (Misc queries) | |||
count of totals for pivot table | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) |