Different values from subset of a table

microsoft excel

I have two tables,

company

shops

and I want to know in the company table the number of different countries per company.

I don`t know how to count the different values for only certain rows.

The solution should be without VBA.

Thank you for your help!

Best Answer

I would use Pivot Tables. The reason being is you don't have to worry about formulas and the Pivot does all the hard work for you. If you update the data all you need to do is click Update on your pivot table and bam, all done.

Basically you would highlight all the information in table 2 and do Insert -> Pivot Table

Leave the options as default (Select a table) and (New Worksheet) then click OK.

Drag Company ID to the Rows block. Drag the Country ID to the Columns block and again to the Values block.

Right click on the Country ID under the Values block and select Value Field Settings

Change the Summarize value field by from Sum to Count and click OK

See the screenshot. This is what you will get.

Screenshot

Related Question