Excel - counting cells containing a text string
 

I'm using excel 2002, and I want to count the number of cells in each row of
a database that contain a particular text string. Can anyone tell me how to
achieve this?

Whole string match:

=COUNTIF(Range,X2)

where X2 houses a criterion string.

Partial string match:

=COUNTIF(Range,X2&"*")

=COUNTIF(Range,"*"&X2)

=COUNTIF(Range,"*"&X2&"*")

Pegasus Host wrote:
I'm using excel 2002, and I want to count the number of cells in each row of
a database that contain a particular text string. Can anyone tell me how to
achieve this?

That's worked thanks, but now I have a follow up question. In front of each
text string that's been found, there is a number which I need to total up.
As an example, the cells contain entries like
9@2 or 21.5 @2 or
I'm able to total the number of cells that contain the text string @2
but how do I total all the digits in front of the @2

"Aladin Akyurek" wrote:

Whole string match:

=COUNTIF(Range,X2)

where X2 houses a criterion string.

Partial string match:

=COUNTIF(Range,X2&"*")

=COUNTIF(Range,"*"&X2)

=COUNTIF(Range,"*"&X2&"*")

Pegasus Host wrote:
I'm using excel 2002, and I want to count the number of cells in each row of
a database that contain a particular text string. Can anyone tell me how to
achieve this?

=SUM(IF(ISNUMBER(SEARCH("@",Range)),--LEFT(Range,SEARCH("@",Range)-1),0))

which you need to confirm with control+shift+enter instead of the usual
enter.

Pegasus Host wrote:
That's worked thanks, but now I have a follow up question. In front of each
text string that's been found, there is a number which I need to total up.
As an example, the cells contain entries like
9@2 or 21.5 @2 or
I'm able to total the number of cells that contain the text string @2
but how do I total all the digits in front of the @2

"Aladin Akyurek" wrote:


Whole string match:

=COUNTIF(Range,X2)

where X2 houses a criterion string.

Partial string match:

=COUNTIF(Range,X2&"*")

=COUNTIF(Range,"*"&X2)

=COUNTIF(Range,"*"&X2&"*")

Pegasus Host wrote:

I'm using excel 2002, and I want to count the number of cells in each row of
a database that contain a particular text string. Can anyone tell me how to
achieve this?

That's worked exactly as I needed - thanks very much.

"Aladin Akyurek" wrote:

=SUM(IF(ISNUMBER(SEARCH("@",Range)),--LEFT(Range,SEARCH("@",Range)-1),0))

which you need to confirm with control+shift+enter instead of the usual
enter.

Pegasus Host wrote:
That's worked thanks, but now I have a follow up question. In front of each
text string that's been found, there is a number which I need to total up.
As an example, the cells contain entries like
9@2 or 21.5 @2 or
I'm able to total the number of cells that contain the text string @2
but how do I total all the digits in front of the @2

"Aladin Akyurek" wrote:


Whole string match:

=COUNTIF(Range,X2)

where X2 houses a criterion string.

Partial string match:

=COUNTIF(Range,X2&"*")

=COUNTIF(Range,"*"&X2)

=COUNTIF(Range,"*"&X2&"*")

Pegasus Host wrote:

I'm using excel 2002, and I want to count the number of cells in each row of
a database that contain a particular text string. Can anyone tell me how to
achieve this?