Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    St. Louis, Missouri, USA
    Posts
    67
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =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)
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    St. Louis, Missouri, USA
    Posts
    67
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
  •