# 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
###### Who is Participating?

x

Commented:
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.

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 Commented:
This worked perfect!  I never would have thought of that one on my own.  Thanks so much for your help.  AWESOME!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.