?
Solved

Excel 2010 Pivot Tables

Posted on 2013-02-01
2
Medium Priority
?
317 Views
Last Modified: 2013-02-04
Is there any way to get a count of total records in a PivotTable that meets my criteria below:

For example, say I have 5 entries for Rita on the account 1234, and 3 entries for Rita on the account 6789.  So I want to set up a pivot table that shows me Rita's accounts with a count of only 2 - 1 for account 1234 and 1 for account 6789.  So I want the account to show up only one time, which is working fine, but then if I include subtotals, I'm getting the totals for all the account records (8) instead of just having the system count each account once.  I can't figure out how to do this.  I keep getting a total count of 8, but that's not what I want.  I have attached a screen shot, too, so you can see another example.  So in the example, I want to count the number of records under each Approver's Name.  There are multiple records for each Approver if I were to click on the plus sign by their name.  The records may appear multiple times in the underlying data source, but I only want it to count one of those.  Hope this is making sense.
pivottable.png
0
Comment
Question by:esu4236
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38846892
You did not provide a true sample file, but generally if you want to show the number of distinct items in a PivotTable, you need a "helper" column in your source data.

Please refer to this sample:

Q-28017392.xlsx

I am assuming a simple data model, in which your data worksheet has columns for name (Col A), account (Col B), and quantity (Col C).  I am also assuming that you want your PT to show, for each name, the number of distinct accounts handled and the sum of quantity overall.

To do this, I added the following formula in Col D:

=1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2)

In Excel 2003 you would have to use a different formula, because COUNTIFS is only available in Excel 207 and later:

=1/SUMPRODUCT(($A$2:$A$15=A2)*($B$2:$B$15=B2))

Now, when creating the PivotTable, use Name as the row field, and sum of Col D for the distinct count, and sum of quantity for the data fields.
0
 

Author Closing Comment

by:esu4236
ID: 38851371
This worked perfect!  I never would have thought of that one on my own.  Thanks so much for your help.  AWESOME!!!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question