Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting occurences of a substring in a range of cells.
Thank you in advance for all replies.
I have a column in a worksheet which contains a series of 2 character codes separated by commas: ac, bg, hj, r4, qt, mr ac,mr,t7 mr,e3,bg search will return a value for starting position in each cell, and countif will return a count, but mixing the two together escapes me. Sure there's a way. (SQL is sooooo much easier.) Jeff N. Cantwell Downtown Little Rock NRA, ARPA, Libertarian '86, '91 300zx 2+2 |
#2
|
|||
|
|||
If, for example, you want to count the number of 'bg's in your range of
cells, try... =COUNTIF(A1:A100,"*bg*") or =COUNTIF(A1:A100,"*"&B1&"*") ....where B1 contains your criterion, such as 'bg'. Hope this helps! In article , Jeff Cantwell wrote: Thank you in advance for all replies. I have a column in a worksheet which contains a series of 2 character codes separated by commas: ac, bg, hj, r4, qt, mr ac,mr,t7 mr,e3,bg search will return a value for starting position in each cell, and countif will return a count, but mixing the two together escapes me. Sure there's a way. (SQL is sooooo much easier.) Jeff N. Cantwell Downtown Little Rock NRA, ARPA, Libertarian '86, '91 300zx 2+2 |
#3
|
|||
|
|||
Hi!
search will return a value for starting position in each cell, and countif will return a count, but mixing the two together escapes me. What do you mean by "mixing the two together" ? Biff "Jeff Cantwell" wrote in message ... Thank you in advance for all replies. I have a column in a worksheet which contains a series of 2 character codes separated by commas: ac, bg, hj, r4, qt, mr ac,mr,t7 mr,e3,bg search will return a value for starting position in each cell, and countif will return a count, but mixing the two together escapes me. Sure there's a way. (SQL is sooooo much easier.) Jeff N. Cantwell Downtown Little Rock NRA, ARPA, Libertarian '86, '91 300zx 2+2 |
#4
|
|||
|
|||
Thank you! That may have been what I was missing.
=IF('0 - 2'!AS2:AS1551="White",COUNTIF('0 - 2'!AQ2:AQ1551,"*AS*"),) I seldom use excel, and am getting a crash course OJT. On Thu, 08 Sep 2005 16:52:49 -0400, Domenic wrote: If, for example, you want to count the number of 'bg's in your range of cells, try... =COUNTIF(A1:A100,"*bg*") or =COUNTIF(A1:A100,"*"&B1&"*") ...where B1 contains your criterion, such as 'bg'. Hope this helps! In article , Jeff Cantwell wrote: Thank you in advance for all replies. I have a column in a worksheet which contains a series of 2 character codes separated by commas: ac, bg, hj, r4, qt, mr ac,mr,t7 mr,e3,bg search will return a value for starting position in each cell, and countif will return a count, but mixing the two together escapes me. Sure there's a way. (SQL is sooooo much easier.) Jeff N. Cantwell Downtown Little Rock NRA, ARPA, Libertarian '86, '91 300zx 2+2 Jeff N. Cantwell Downtown Little Rock NRA, ARPA, Libertarian '86, '91 300zx 2+2 |
#5
|
|||
|
|||
Your IF statement doesn't seem correct. What is it you're trying to do?
Do you want to count the number of 'AS' in your range of cells if AS2:AS1551 contains 'White'? If so, try... =IF(COUNTIF('0 - 2'!AS2:AS1551,"White"),COUNTIF('0 - 2'!AQ2:AQ1551,"*AS*"),"") Hope this helps! In article , Jeff Cantwell wrote: Thank you! That may have been what I was missing. =IF('0 - 2'!AS2:AS1551="White",COUNTIF('0 - 2'!AQ2:AQ1551,"*AS*"),) I seldom use excel, and am getting a crash course OJT. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif argument for 3 occurences of which 1 refers to a range | Excel Discussion (Misc queries) | |||
counting a cell range within a range | Excel Worksheet Functions | |||
Counting within a filtered range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |