Solved

Excel 2010 Pivot Tables

Posted on 2013-02-01
2
299 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 92

Accepted Solution

by:
Patrick Matthews earned 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

749 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