CODEWITHSUNDEEP
excel
Pr0no
Pr0no

Reputation: 4109

Count the number of companies per country in Excel

I have the following 2 excel sheets in the same workbook:

companies:

    A          B
1 COMPANY   COUNTRY
2 Apple     USA
3 Microsoft USA
4 Samsung   Taiwan
5 Philips   Netherlands
6 Tulip     Netherlands

countries:

    A             B
1 COUNTRY      NUM_COMP
2 USA          2*
3 Taiwan       1*
4 Netherlands  2*

In sheet countries column B I need the number of companies per country. I have now counted them by hand, but I need a formula for cells B2 to B4 to do this automagically, since the actual sheet is much, much longer than this example.

Can anyone help me? Your help is greatly appreciated.

Upvotes: 0

Views: 4642

Answers (2)

barry houdini
barry houdini

Reputation: 46401

For the number of different companies per country try this formula in B2

=SUM(IF(FREQUENCY(IF(companies!B$2:B$2265=A2,MATCH(companies!A$2:A$2265,companies!A$2:A$2265,0)),ROW(companies!A$2:A$2265)-ROW(companies!A$2)+1),1))

That's an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.

Now copy formula down column

Upvotes: 2

Sky Diver
Sky Diver

Reputation: 36

Function 'Countif' should be helpfull :)

=COUNTIF(B:B;B2)

cheers Sky

Upvotes: 2

Related Questions