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

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

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

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

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.