counting multiple entries to equal 1 item in pivot table

[Beany27]

Hi Everyone,

I am pretty much useless at formula writing. I need a pivot table to be able to calculate the following data (below) as number of different subjects (count all duplicates as 1 but only for the specified teacher( eg:10 subjects instead of 12 (total number) or 7 (counting ALL math as 1) Does this make any sense?


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

 

[Andrew Poulsom]

Welcome to MrExcel.

If your data is in the range A2:C13, enter headings in D1:E1 and these formulas in D2 and E2 copied down:

=1/COUNTIFS(A$2:A$13,A2,B$2:B$13,B2)

=1/COUNTIF(B$2:B$13,B2)

Add those columns to the data area in your pivot table using the function Sum.

 

[Beany27]

Welcome to MrExcel.

If your data is in the range A2:C13, enter headings in D1:E1 and these formulas in D2 and E2 copied down:

=1/COUNTIFS(A$2:A$13,A2,B$2:B$13,B2)

=1/COUNTIF(B$2:B$13,B2)

Add those columns to the data area in your pivot table using the function Sum.

Thanks Andrew,

, I think I sort of understand what you are trying to do (I'm a very basic excel user), but I cannot include any other columns in my spreadsheet sheet as this is a document that is being used by many people, is there a way of doing this in the pivot table or another table. (BTW I have hundreds of rows of data, but due to our strict information security policies, I gave a very basic example.)

 

[Andrew Poulsom]

It's not possible directly in the pivot table, sorry.