Distinct values in excel pivot

[saurabhlotankar]

Hello,

I have attached an excel workbook here.

I have some data in it. I am trying to create a pivot table which will give me count of unique number of Categories mentioned in column C.

I have given result which I am looking for in result worksheet.

I am looking to create it via pivot only. Please help.


Thanks.

[romperstomper]

What version of Excel are you actually using?

[saurabhlotankar]

excel 2010

[romperstomper]

Do you have the power pivot add-in available to you?

[saurabhlotankar]

No Power pivot available. I can understand that with power pivot it would have been easy.

Can a vba function be built which will work in sync with normal pivot. Just a thought.

[romperstomper]

Add a new column to the source data using this formula:

=1/COUNTIFS($A$2:$A$24,A2,$C$2:$C$24,C2)

and then sum it in your pivot table. Note that it is specific to the unique count of categories by name. If you add other groupings to the pivot it will not work.