General discussion


Counting Unique Entries in Pivot Tables

By bbusselman ·
I am looking for help with Excel pivot tables. A simple example of the table would be - I want to know how many different cities were visited the sales staff per country and have the following data from logs:

Who City Country
Bob Dallas USA
Bob Houston USA
Sue Dallas USA
Jim Mex City Mexico

I can create a pivot table with first column of "Country" and put the City in the data portion of the table and it will default to count. Total count is 4 representing how many times this column is nonempty but only 3 cities were visited. How can I get the answer of 3?

(My real problem is a report with TI regions, suppliers used and total $$ spent per supplier. I want to know how many unique suppliers are used across the regions. Maybe pivot table is not the right answer but I used this to summarize the individual purchase order values per supplier). I got my answers this time by concatenating fields (city-country in the example), makea pivot table with city-country on far left, copied and paste-special the entire table to make values, split the city country with "columns to text" and used this table to count "cities per country". This was quite time consuming and painful (easy to make mistake and then have to figure out what exactly happened...)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Counting Unique Entries in Pivot Tables

by Garlin In reply to Counting Unique Entries i ...

There are two things you can try.

I admit I did not fully understand what you were trying to do, but I do know how to get the cities to add up to 3 instead of 4.

Add a column to your data called Count. Put a 1 in each row, or a 1 in each row with a city using a formula like (if city cell is not empty, 1, 0)

Then make city one of your "row" fields, so now your pivot table will be by country by city. Then Sum the Count field. Where a city is repeated it will only occupy one row, and the multiple ones will be added together. (You may be able to accomplish the same thing by counting another field, such as the $$ field you mentioned, instead of summing Count.)

Related Discussions

Related Forums