• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique values in pivot table

F

francisng

New Member
I'm working on a dataset using pivot table. I would like to count unique text values only in a column, unfortunately, pivot table's count function would count all identical text values as well. May I know how will I be able to use pivot table to count unique text values only?


For eg:


Company Reinsurer

TENET PARTNER RE GROUP

UOI PARTNER RE GROUP

QBE QBE GROUP

QBE QBE GROUP

QBE QBE GROUP

QBE QBE GROUP


The pivot table will produce the following count:

Reinsurer Count(Company)

PARTNER RE GROUP 2

QBE GROUP 4


However, I would like pivot table to provide 1 count only for non-unique text value.


How is it possible to do so? Thanks much in advance.
 
In your data table (that feeds PivotTable), include a helper column with something like:

=COUNTIF(A$2:A2,A2)

Copied down. Make sure the helper column has a name, like "Unique Counter"

Add this field to the page area of your PivotTable, and set it to "1".
 
Hello,

For a flexible and automatic solution see:

http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html


Let me know if it did work for you.


Regards
 
You must log in or register to reply here.
Back
Top