+ Reply to Thread
Results 1 to 7 of 7

Count partial text matches in adjacent column

  1. #1
    Registered User
    Join Date
    08-11-2014
    Location
    St. Albans, VT
    MS-Off Ver
    Mac Office 2011
    Posts
    3

    Count partial text matches in adjacent column

    I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.

    A B
    FB Milton v Town PHOTO
    BS Fairfax v South
    BS North v Town
    BS Milton v South PHOTO
    FB North v Milton PHOTO
    FB Milton v South

    I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc., but have gotten hopelessly confused. Thanks for any help!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Count partial text matches in adjacent column

    Assuming that FB will always be first you should be able to do something like:
    =countifs(A1:A10,"*FB*Milton*",B1:B10,"PHOTO")
    Edit, in any order, you could use:
    =sum(countifs(A1:A10,{"*FB*Milton*","*Milton*FB*"},B1:B10,"PHOTO"))

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count partial text matches in adjacent column

    Maybe this is what you are looking for:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count partial text matches in adjacent column

    =COUNTIFS(A1:A6,"fb *milton*",B1:B6,"photo") should work
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    08-11-2014
    Location
    St. Albans, VT
    MS-Off Ver
    Mac Office 2011
    Posts
    3

    Re: Count partial text matches in adjacent column

    That seems to do the trick. Thanks very much for the prompt help!

  6. #6
    Registered User
    Join Date
    08-11-2014
    Location
    St. Albans, VT
    MS-Off Ver
    Mac Office 2011
    Posts
    3

    Re: Count partial text matches in adjacent column

    Thanks! The post did not make clear that there were two columns of info. Appreciate it!

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count partial text matches in adjacent column

    perfectly clear to me anyway thanks for the feed back

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula that recoginizes partial text matches and concatenates them
    By Shadefalcon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-02-2014, 04:11 PM
  2. Summing a column where matches adjacent column and colating results
    By rlamb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 08:24 AM
  3. Hard? SUM the values of a column that matches a flag in an adjacent column
    By Nokao in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-07-2012, 11:29 AM
  4. Replies: 2
    Last Post: 10-30-2007, 12:12 PM
  5. [SOLVED] how do I count the number of times text in column A matches text i
    By Sheila in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2005, 06:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1