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 <jb51i1tguplmr15s93hnhvuo6jkkatqd5j@4ax.com>,
Jeff Cantwell <jeff-cantwell@swbell.net> 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

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 <domenic22@sympatico.ca>
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 <jb51i1tguplmr15s93hnhvuo6jkkatqd5j@4ax.com>,
> Jeff Cantwell <jeff-cantwell@swbell.net> 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 <2p91i1tb5gagiu64kektm0eq8hrk2mp3ln@4ax.com>,
Jeff Cantwell <jeff-cantwell@swbell.net> 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.

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" <jeff-cantwell@swbell.net> wrote in message
news:jb51i1tguplmr15s93hnhvuo6jkkatqd5j@4ax.com...
> 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