software

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: www.excelforum.com/member.php...foamp;userid=4029
View this thread: www.excelforum.com/showthread...hreadid=507326In a cell somewhere on your sheet place:
=SUMPRODUCT(LEN(C2:C2700)-LEN(SUBSTITUTE(C2:C2700,I2,quot;quot;)))/LEN(I2)

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

HTH
GerryK

quot;Ingeniero1quot; wrote:

gt;
gt; I have 2700 rows where one of the columns is a description of an
gt; instrument. As you can imagine, the sky is the limit with the way the
gt; descriptions are written.
gt;
gt; Using the Auto-Filter feature, I can filter the cells in this column
gt; according to whether they contain or not-contain any text I choose. But
gt; then I have to count the rows returned.
gt;
gt; What I am looking for is to count the cells that contain a word or
gt; combination of words within a longer string of text. Is there a way to
gt; use COUNTIF based on any of the contents of a cell?
gt;
gt; Thanks!
gt;
gt; Alex
gt;
gt;
gt; --
gt; Ingeniero1
gt; ------------------------------------------------------------------------
gt; Ingeniero1's Profile: www.excelforum.com/member.php...foamp;userid=4029
gt; View this thread: www.excelforum.com/showthread...hreadid=507326
gt;
gt;

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,quot;*violin*quot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ingeniero1quot; gt; wrote
in message ...
gt;
gt; I have 2700 rows where one of the columns is a description of an
gt; instrument. As you can imagine, the sky is the limit with the way the
gt; descriptions are written.
gt;
gt; Using the Auto-Filter feature, I can filter the cells in this column
gt; according to whether they contain or not-contain any text I choose. But
gt; then I have to count the rows returned.
gt;
gt; What I am looking for is to count the cells that contain a word or
gt; combination of words within a longer string of text. Is there a way to
gt; use COUNTIF based on any of the contents of a cell?
gt;
gt; Thanks!
gt;
gt; Alex
gt;
gt;
gt; --
gt; Ingeniero1
gt; ------------------------------------------------------------------------
gt; Ingeniero1's Profile:
www.excelforum.com/member.php...foamp;userid=4029
gt; View this thread: www.excelforum.com/showthread...hreadid=507326
gt;

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: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=507326You 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
quot;Ingeniero1quot; wrote:

gt;
gt; I have 2700 rows where one of the columns is a description of an
gt; instrument. As you can imagine, the sky is the limit with the way the
gt; descriptions are written.
gt;
gt; Using the Auto-Filter feature, I can filter the cells in this column
gt; according to whether they contain or not-contain any text I choose. But
gt; then I have to count the rows returned.
gt;
gt; What I am looking for is to count the cells that contain a word or
gt; combination of words within a longer string of text. Is there a way to
gt; use COUNTIF based on any of the contents of a cell?
gt;
gt; Thanks!
gt;
gt; Alex
gt;
gt;
gt; --
gt; Ingeniero1
gt; ------------------------------------------------------------------------
gt; Ingeniero1's Profile: www.excelforum.com/member.php...foamp;userid=4029
gt; View this thread: www.excelforum.com/showthread...hreadid=507326
gt;
gt;


Timely good replies!

This one works best for what I need.
=COUNTIF(B:B,quot;*violin*quot;) / 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: www.excelforum.com/member.php...foamp;userid=4029
View this thread: www.excelforum.com/showthread...hreadid=507326