Searching a text string in a range of cells.

G

Guest

How do i search for something within a text string in a cell which is in a
range of cells.

so if a cell contains the names "jim, bob, tom" what formula can i use to
just search for "bob"
I am currently using a SUMIF function which is only picking up cells that
only contain "bob" on its own.
 
G

Guest

=SUMIF(A1:A8,"*bob*",C1:C8)

will sum values in C1:C8 where A1:A8 contain "bob"

=COUNTIF(A1:A8,"*bob*")

will count occurences of "bob" in A1:A8 BUT only one occurence per cell i.e.
"bob,tom,jim,bob" in a cell will count as 1

HTH
 
G

Guest

Thank you very much! That worked perfectly!

Toppers said:
=SUMIF(A1:A8,"*bob*",C1:C8)

will sum values in C1:C8 where A1:A8 contain "bob"

=COUNTIF(A1:A8,"*bob*")

will count occurences of "bob" in A1:A8 BUT only one occurence per cell i.e.
"bob,tom,jim,bob" in a cell will count as 1

HTH
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search for specific text within last non blank cell in a range 2
SUMIF for text 6
VBA set thick borders around non empty cells in range 0
Searching the formula (not the value) for a text string 1
Conditionally format a cell using a list of data defined in a rang 2
SUMPRODUCT formula I think w/ wild card & number vs number as text 8
count - but exclude counting cells with a formula 4
Comparing the first 2 characters of a coloumn to a string 1

Top