Assuming that the company/industry data is in A1:B6 (including the
headers) on Sheet2, the distinct company identifiers in column A from
row 2 downwards on Sheet1, and you download and install the free
morefunc.xll add-in...

B2:

=COUNTDIFF(IF(Sheet2!$A$2:$A$6=A2,
IF(Sheet2!$B$2:$B$6<"",
LEFT(Sheet2!$B$2:$B$6,2))),FALSE,FALSE)

which needs to be confirmed with control+shift+enter (not with enter)
then copied down

myra_deV wrote:
hi, i have the following problem

worksheet 1:
company number of industries
1000 ?
1010 ?
1020 ?

worksheet 2:
company industry
1000 2115
1000 2350
1000 2140
1010 3500
1010 3510

i would like to count per company the number of industry codes, differing at
the first 2 digits.
so for company "1000" count would be 2. (21.. and 23..), but for "1010"
count would be only 1.
a normal countif function appears inappropriate, i tried

count(if(AND(company="1000";left(industry..;2)<le ft(industry..;2));industry...)
but i excel stops once it has found the company in row 1.

thanx.