[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Excel 2010 Pivot Tables

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
esu4236
Asked:
esu4236
1 Solution
 
Patrick MatthewsCommented:
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
 
esu4236Author Commented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now