Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF((Sheet2!A$2:A$6=A2)*(LEN(Sheet 2!B$2:B$6)1),MATCH(LEFT(Sheet2!B$2:B$6,2),LEFT(Sh eet2!B$2:B$6,2),0)),MATCH(LEFT(Sheet2!B$2:B$6,2),L EFT(Sheet2!B$2:B$6,2),0))0))

This is the type of formula where SHORT sheet names can make a big
difference!

This portion:

*(LEN(Sheet2!B$2:B$6)1)

may not be necessary. I added it for a little more robustness. It accounts
for cells that might be empty or may not have at least 2 digits.

Biff

"myra_deV" wrote in message
...
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.