0

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
0

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

0

I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.