counting occurences in a col in sheet1, by referencing cols in she

[Dez]

Hi i'm having probelms getting around this.

Need to match 3 columns between two sheets in the same workbook to count
specific occurences. below is how i've set it up.

in sheet 1, i've got the following data for eg.....
SHEET 1
Company Country Industry Tier
1 air asai malaysia airline 1
2 china air china airline 1
3 sia singapore airline 3
4 hanjin malaysia shipping 1
5 wanhai china shipping 3
6 cosco singapore shipping 3
7 vitol malaysia trading 1
8 trafigura china trading 3
9 sinopec singapore trading 3
10 origin malaysia producer 1
11 sonangol china producer 2
12 petrochina singapore producer 2


i've got 3 other sheets where i've filtered the companies accoding to the
tiers. so 3 sheets for 3 tiers. so for example in sheet 2 i've got all the
tier 1 companies, sheet 3 tier 2, and sheet 4, tier 3. (Sheet 2 below)

what i need to do is to count the number of names in each country for the
various industry types by referencing or matching the names to the
corresponding data in sheet 1. how do i do this to return the appropriate
values in the (count) brackets below? you will have my eternal gratitude if
you could solve this for me!

thanks ahead....


SHEET 2

airline (count) (count) (count)
shipping (count) (count) (count)
trading (count) (count) (count)
producer (count) (count) (count)

malaysia china singapore


1 air asai
2 china air
3
4 hanjin
5
6
7 vitol
8
9
10 origin
11
12

 

[Martin Fishlock]

Hello Dez:

Try using the sumproduct function getting boolean values as follows:

in tier1

=sumproduct(--(b1:b200="air asai"),--(d1:d200=1))

"air asai" and 1 can be replaced with cell refences to get the referenced
data.

Post what the answer should look like and it will help a lot.

 

[Dez]

Hi Martin, cheers.

this is what it would look like in sheet 2.


A B
C

airline 1 1 0
shipping 0 0 1
trading 1 0 0
producer 1 0 0

malaysia china singapore


1 air asai
2 china air
3
4 hanjin
5
6
7 vitol
8
9
10 origin
11
12

it the sheet above (Sheet 2), take the Malaysia column, how company vitol is
reflected is through the "IF" function --> =if(Sheet1!$C7
[Country]=Sheet2$[Cell with the country Malaysia], Sheet1!A7[cell that
contains name of the company],"")

i drag this function down so that the cells in that column (eg col A above)
will pick up the names of the companies in that country that have been
categorised into a specific tier in Sheet 1.

then what i want to do above it is to count the names in that column by the
sepcific categories of industies as listed in teh cells above the country
heading. so in each row of the particular cell, there will be a count for the
various industries above each country.

the sumproduct funtion does not return a value what i'm looking for.

i would love to send you the actual sheet if you could send an email to
(e-mail address removed) for me to reply to. thanks.

Hello Dez:

Try using the sumproduct function getting boolean values as follows:

in tier1

=sumproduct(--(b1:b200="air asai"),--(d1:d200=1))

"air asai" and 1 can be replaced with cell refences to get the referenced
data.

Post what the answer should look like and it will help a lot.

A B C D
Company Country Industry Tier