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…)
Thanks,
Bobby
