Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
=COUNTIF(Sheet2!A:A,A2)
and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
One of us has really missed the boat on this!
Biff "Bob Phillips" wrote in message ... =COUNTIF(Sheet2!A:A,A2) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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) "Biff" wrote in message ... One of us has really missed the boat on this! Biff "Bob Phillips" wrote in message ... =COUNTIF(Sheet2!A:A,A2) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif problem
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 "myra_deV" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing csv files, problem with date formats | Excel Worksheet Functions | |||
CountIf & null values problem | Excel Worksheet Functions | |||
countif problem | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
problem with countif | Excel Worksheet Functions |