THANX!! that really did the trick.

an additional question though:
some industry code are 0100 which in excel becomes 100
does it still count these? without confusing them with 1000. since both
left(cell,2) would be "10".
does that LEN formula help overcome this?

TC

"Biff" wrote:

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.