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 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:
=SUM(IF(Sheet1!$A$2:$A$10=A2,1/COUNTIFS(Sheet1!$B$2:$B$10,Sheet1!$B$2:$B$10,Sheet1!$A$2:$A$10,A2)))
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:
So on sheet two I place the formula in C2 and copy down: