countif problem

[Guest]

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)<>left(industry..;2));industry...)
but i excel stops once it has found the company in row 1.

thanx.

 

[Bob Phillips]

=COUNTIF(Sheet2!A:A,A2)

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Biff]

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(Sheet2!B$2:B$6)>1),MATCH(LEFT(Sheet2!B$2:B$6,2),LEFT(Sheet2!B$2:B$6,2),0)),MATCH(LEFT(Sheet2!B$2:B$6,2),LEFT(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

 

[Biff]

One of us has really missed the boat on this!

Biff

 

[Aladin Akyurek]

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

 

[Bob Phillips]

I originally started down the path you took, but then decided it was much
much simpler <vbg>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

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]

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".

That would be a problem. To overcome that you could enter the industry codes
as TEXT.

Either preformat the cells as TEXT or when you enter a code preceeed it with
an apostrophe ' : '0100

Biff