Hi,
I am relatively new to the more complex side of excel. I hope my questions are not too simple.
I am using excel 2007 on a winXP machine.
OK, I have a large table holding information about products. For each product there are many columns: country, stage of development, company developing it etc.
I need to be able to look at the data and slice based on different criteria. So I turned to pivot tables.
Lets say I want to make a pivot table that counts how many *companies* are in each *country*. Of course, I have multiple rows: if company A from China makes three products I have three lines (one for each product), on each of them the country is duplicated (all three rows will have 'China' as country) and the company name is duplicated (all three rows have 'A' as the company name).
Now, I want to generate a pivot table and I select 'Country' as my 'row label', and 'Count of Company' as my Value. (I do not need a column label right now).
What I end up with, for the country 'China', is 3 companies. Makes sense, as there are three lines.
How can I get the answer I need in the pivot table? I need to be able to show in the pivot table the unique number of companies in each country (and then use the rest of the pivot table to slice further, for example, how many companies in China make a product of type Z). Of course I have multiple companies per country, and multiple products per company, which means, I have multiple lines for the same company.
Thanks!
Y
I am relatively new to the more complex side of excel. I hope my questions are not too simple.
I am using excel 2007 on a winXP machine.
OK, I have a large table holding information about products. For each product there are many columns: country, stage of development, company developing it etc.
I need to be able to look at the data and slice based on different criteria. So I turned to pivot tables.
Lets say I want to make a pivot table that counts how many *companies* are in each *country*. Of course, I have multiple rows: if company A from China makes three products I have three lines (one for each product), on each of them the country is duplicated (all three rows will have 'China' as country) and the company name is duplicated (all three rows have 'A' as the company name).
Now, I want to generate a pivot table and I select 'Country' as my 'row label', and 'Count of Company' as my Value. (I do not need a column label right now).
What I end up with, for the country 'China', is 3 companies. Makes sense, as there are three lines.
How can I get the answer I need in the pivot table? I need to be able to show in the pivot table the unique number of companies in each country (and then use the rest of the pivot table to slice further, for example, how many companies in China make a product of type Z). Of course I have multiple companies per country, and multiple products per company, which means, I have multiple lines for the same company.
Thanks!
Y
