[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel 2010 Pivot Tables

Posted on 2013-02-01
Medium Priority
335 Views
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
Question by:esu4236

LVL 93

Accepted Solution

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.

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

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

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month18 days, 16 hours left to enroll