Excel – How to get count in pivot table that does NOT count duplicate entries

microsoft-excel-2007pivot table

I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be counted once regardless if there are duplicate records for that combination.

For example, with the raw data below, I would like the pivot table to count 10 (not 12) total records for combinations of teacher and subject. That is, (Teacher 1, Math) only gets counted once, and the same for (Teacher 3, English).

Teacher 1   Math     Student 1
Teacher 1   Math     Student 2
Teacher 1   English  Student 3
Teacher 1   History  Student 4
Teacher 2   Math     Student 5
Teacher 2   Science  Student 6
Teacher 2   Biology  Student 7
Teacher 3   Math     Student 8
Teacher 3   Science  Student 9
Teacher 3   English  Student 10
Teacher 3   English  Student 11
Teacher 3   Math     Student 12

How can I set up my pivot table to do this?

Best Answer

Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.

Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

Then create your pivot based on the 1st and 3rd column.

enter image description here

Related Solutions

Excel – Sort Excel Pivot Table by Percentage of Count

Interesting challenge. Some of the issues include:

Field calculations do not have enough flexibility to get what you need
Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.

I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):

Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
In your new table, insert a calculation for %NotApproved
Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
Sort the Manager Names in Descending order by %NotApproved

Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...

Select the "Insert" Ribbon and click on "Table" ...

You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...

=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100

When you hit enter, it is automatically filled down in the table. This formula does:

IF([@[TS Approved?]]="No",1,0) If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]]) Determines how many times the manager in this row appears in the table.
Result from 1 divided by the result from 2 times 100

The table now looks like this ...

Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...

... and sort it ...

... to get this ...

Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.

Excel – Calculating ratio of two columns in Excel pivot table

The percentage of True items in a list is the average of zeros and ones, where True is represented by 1 and False by 0.

Just drag the Won field into the values area, and change the aggregation from Sum to Average:

Got this from my good pal Doug Glancy's blog at http://yoursumbuddy.com/percent-of-true-items-in-a-pivot-table-field/

Best Answer

Related Solutions

Excel – Sort Excel Pivot Table by Percentage of Count

Excel – Calculating ratio of two columns in Excel pivot table

Related Question