# Thread: Counting text that's only part of the cell

1. ## Counting text that's only part of the cell

Hi all,

I'm trying to count cells depending on whether a range of cells contains certain text that's only part of the cell contents. For instance, if I search for "Train," I need to count the cell that says "plane, train, and bus" and the one that says "I need to train that dog."

Also, is there a way to use this result to sum the numbers in other cells (basically two other cells in each of the rows that have "Train" in them)?

Many thanks,

Lind

2. A thought that comes to mind:

=ISNUMBER(FIND("TRAIN",UPPER(A1))) in the B column and fill down.

Then, =sumproduct(1*B1:B10)

This will count "training" also.

But, you could modify to something like this: =OR(ISNUMBER(FIND("TRAIN.",UPPER(A1))),ISNUMBER(FI ND("TRAIN ",UPPER(A1))))
which would count train with a space and train ending the sentence.

Hope this gives you some ideas.

3. =COUNTIF(A1:A100,"*train*")
would do the first part. If you need to sum two columns based on the same criteria then you can add two SUMIF formulas together:
=SUMIF(A1:A100,"*train*",B1:B100)+SUMIF(A1:A100,"* train*",C1:C100)
or if the columns are adjacent, use SUMPRODUCT
=SUMPRODUCT((ISNUMBER(SEARCH("train",A1:A100)))*B1 :C100)

4. Thanks! That's exactly what I needed.
lind

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•