I have two tables,



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!


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.



Use this array formula:


Being an array formula it needs to be confirmed with Ctrl-Shift-Enter. If done correctly Excel will put {} around the formula.

Sheet1 is my data sheet:

enter image description here

So on sheet two I place the formula in C2 and copy down:

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.