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

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?

• Thanks David, I really am useless when it comes to excel actually pretty much anything technical – user379028 Oct 13 '14 at 10:16
• No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table? – Raystafarian Oct 13 '14 at 11:17
• Total Subjects Total Students – user379028 Oct 13 '14 at 11:39
• the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated. – user379028 Oct 13 '14 at 11:42
• I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit. – Excellll Oct 13 '14 at 15:52

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