CODEWITHSUNDEEP
excel
Pr0no
Pr0no

Reputation: 4109

Count companies per country based on certain criteria (in Excel)

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

companies:

    A          B            C         D
1 COMPANY   COUNTRY      SECTOR    SUBSECTOR
2 Apple     USA          Hardware  Consumer
3 Microsoft USA          Software  Consumer and Business
4 Samsung   Taiwan       Hardware  Business
5 Philips   Netherlands  Hardware  Consumer and Business
6 Tulip     Netherlands  Hardware  Business

countries:

    A             B         C           D
1 COUNTRY      NUM_COMP  NUM_SECTOR  NUM_SUB
2 USA             2         1           0
3 Taiwan          1         1           0
4 Netherlands     2         2           1

In sheet countries column B I need the number of companies per country. Thanks to @barry houdini I use the following:

=COUNTIF(companies!B$2:B$4;A2)

However, I need to additional counts. In column C: a count of companies per country in the Hardware sector. In column D: a count of companies per country that are in the Hardware sector and in the Consumer and Business subsector. I have made these counts manually above, but need a formula to count them automagically.

Can anyone help me on my way? Your help is greatly appreciated.

Upvotes: 0

Views: 286

Answers (1)

barry houdini
barry houdini

Reputation: 46401

For countries in hardware sector try COUNTIFS like this

=COUNTIFS(companies!B$2:B$100;A2;companies!C$2:C$100;"hardware")

You can add more conditions so for your second count try

=COUNTIFS(companies!B$2:B$100;A2;companies!C$2:C$100;"hardware";companies!D$2:D$100;"Consumer and Business")

You need Excel 2007 or later to use COUNTIFS

Upvotes: 1

Related Questions