COUNTIF according to presence of string within text
 

I have 2700 rows where one of the columns is a description of an
instrument. As you can imagine, the sky is the limit with the way the
descriptions are written.

Using the Auto-Filter feature, I can filter the cells in this column
according to whether they contain or not-contain any text I choose. But
then I have to count the rows returned.

What I am looking for is to count the cells that contain a word or
combination of words within a longer string of text. Is there a way to
use COUNTIF based on any of the contents of a cell?

Thanks!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=507326

COUNTIF according to presence of string within text
 

In a cell somewhere on your sheet place:
=SUMPRODUCT(LEN(C2:C2700)-LEN(SUBSTITUTE(C2:C2700,I2,"")))/LEN(I2)

I2 is the cell to enter text for searching column C (Modify as needed)

HTH
GerryK

"Ingeniero1" wrote:


I have 2700 rows where one of the columns is a description of an
instrument. As you can imagine, the sky is the limit with the way the
descriptions are written.

Using the Auto-Filter feature, I can filter the cells in this column
according to whether they contain or not-contain any text I choose. But
then I have to count the rows returned.

What I am looking for is to count the cells that contain a word or
combination of words within a longer string of text. Is there a way to
use COUNTIF based on any of the contents of a cell?

Thanks!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=507326

COUNTIF according to presence of string within text
 

If you want to count them after filtering, take a look at SUBTOTAL in Help,
it will do what you want.

If you want to count them without filtering, try

=COUNTIF(B:B,"*violin*")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ingeniero1" wrote
in message ...

I have 2700 rows where one of the columns is a description of an
instrument. As you can imagine, the sky is the limit with the way the
descriptions are written.

Using the Auto-Filter feature, I can filter the cells in this column
according to whether they contain or not-contain any text I choose. But
then I have to count the rows returned.

What I am looking for is to count the cells that contain a word or
combination of words within a longer string of text. Is there a way to
use COUNTIF based on any of the contents of a cell?

Thanks!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=507326

COUNTIF according to presence of string within text
 

Alex

If you're already using AutoFilter, you can probably use this version
of the SUBTOTAL function:

With a data list in Cells A5:A1000

A2: =SUBTOTAL(3,A5:A1000)-1
The minus 1 is to remove the Header count.

After filtering, the formula will return the count of the un-hidden
records.


Other options for the first argument of the the SUBTOTAL function:
Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=507326

COUNTIF according to presence of string within text
 

You can also use the SUBTOTAL formula with your AutoFilter sort, as it
ignores any hidden rows...only counting the ones you've filtered out.....

=SUBTOTAL(2,YourRange)


Vaya con Dios,
Chuck, CABGx3



"Ingeniero1" wrote:


I have 2700 rows where one of the columns is a description of an
instrument. As you can imagine, the sky is the limit with the way the
descriptions are written.

Using the Auto-Filter feature, I can filter the cells in this column
according to whether they contain or not-contain any text I choose. But
then I have to count the rows returned.

What I am looking for is to count the cells that contain a word or
combination of words within a longer string of text. Is there a way to
use COUNTIF based on any of the contents of a cell?

Thanks!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=507326

COUNTIF according to presence of string within text
 

Timely good replies!

This one works best for what I need.
=COUNTIF(B:B,"*violin*") / Bob P.

It even works entering (within asterisks) what I want to search in
another cell.
=COUNTIF(B:B,M2)
and M2 will have *search text*

I entered 10 *search text* in M2:M11 and got a list of the occurrences
for those 10.

Thanks!!


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=507326