+ Reply to Thread
Results 1 to 15 of 15

Countif exact phrase within a string of text

  1. #1
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Countif exact phrase within a string of text

    I have a table of yellow cells which contain different questions candidates have struggled with and require help on.
    I would like to populate the blue table quickly by using a formula that counts how many times the blue questions appear in the yellow list.

    As you can see from the formula I have tried to use, my problem is with the wildcard. When it tries to count the number of times "1" appears in the yellow cells it ends up counting other values of the 1 appearing in other cells, possibly those written as a 10, 11, 12 etc.

    Qns.PNG

    I would rather avoid splitting the yellow cell text into columns at all costs.

    Any help would be greatly appreciated!

    Thank you.
    Attached Files Attached Files
    Last edited by LDHall; 04-03-2020 at 10:13 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Countif exact phrase within a string of text

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    Thank you! Now posted a sheet.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Countif exact phrase within a string of text

    How about
    =SUM(ISNUMBER(SEARCH(" "&D2&","," "&$B$3:$B$11&","))+0)

  5. #5
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    Thank you for a quick reply! That appears to work for some but not others. It doesn't work for counting the number of times 2b occurs for example.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Countif exact phrase within a string of text

    It works for me, unless I'm missing something
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    1
    2
    Candidate Question requiring help
    1
    2a
    2b
    3
    4a
    4b
    5
    6
    7a
    7b
    7c
    8
    9
    10
    11
    12a
    12b
    13
    3
    Candidate 1 1, 2a, 11
    1
    1
    3
    1
    1
    2
    0
    2
    1
    2
    3
    2
    1
    5
    3
    0
    0
    3
    4
    Candidate 2 6, 10
    5
    Candidate 3 2b, 3, 4a, 6, 7b, 7c, 8, 9, 10, 11
    6
    Candidate 4
    11
    7
    Candidate 5
    13
    8
    Candidate 6 4b, 7c, 8, 10
    9
    Candidate 7 2b
    10
    Candidate 8 10, 13
    11
    Candidate 9 2b, 4b, 7a, 7b, 7c, 10, 13
    Sheet: Sheet1

  7. #7
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    I might be missing something myself - did you simply use the formula you mentioned above to be written in D3 and then drag across from D3 to U3?

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Countif exact phrase within a string of text

    I used Fluff's formula too, pasted it into D3 and dragged it across to U3 and it returned the correct results.
    Perhaps you can upload another workbook showing where it fails or provide an explanation of under which circumstances it is failing?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Countif exact phrase within a string of text

    Quote Originally Posted by LDHall View Post
    did you simply use the formula you mentioned above to be written in D3 and then drag across from D3 to U3?
    Yup

    Did you copy/paste the formula, or re-type it?

  10. #10
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    Haha, I am so confused! I've attached my new sheet with the formula in, as well as the photo below, so you can see it isn't working on mine.

    Capture.PNG
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Countif exact phrase within a string of text

    Try confirming the formula in D3 with Ctrl Shift Enter & then drag it across.

  12. #12
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    THAT fixes it! Thank you!! Do you know why the formula wasn't applying correctly before?

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Countif exact phrase within a string of text

    FYI, when I opened the workbook in post #10 the formula had the @ symbol before $B$3:$B$11 like so...
    =SUM(ISNUMBER(SEARCH(" "&D2&","," "&@$B$3:$B$11&","))+0)
    not sure how it got there but when I removed it in cell D3 and dragged across it returned the correct values.

  14. #14
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Countif exact phrase within a string of text

    Thank you - really not sure how that's happened though as it isn't there on the sheet when I open it up.

    Oh well!

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Countif exact phrase within a string of text

    Do you know why the formula wasn't applying correctly before?
    It's an array formula & needs CSE entry.
    However anyone on 365 with the dynamic array functions no longer needs to use CSE (in the main), so I tend to forget to mention it

    The @ symbol will be added by Xl if opened in 365 as it assumes the formula is using something called Implicit Intersection.

+ 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. [SOLVED] Macro to find exact phrase
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2015, 01:26 PM
  2. Find an exact word within a text string
    By juanmimr in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-08-2013, 10:30 AM
  3. [SOLVED] Find Exact Match Text String Using Formula
    By sweetrevelation in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2012, 10:53 AM
  4. Conditional Formating for EXACT word or phrase
    By elfvis in forum Excel General
    Replies: 6
    Last Post: 11-17-2011, 03:31 PM
  5. Assist in modifying code to locate an exact phrase in a cell
    By Jsin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2010, 02:52 PM
  6. Function to return text from phrase X to phrase Y
    By razr in forum Excel General
    Replies: 3
    Last Post: 05-06-2009, 05:52 PM
  7. Extracting an exact phrase from a Cell
    By mazalam in forum Excel General
    Replies: 5
    Last Post: 09-01-2005, 04:05 PM

Tags for this Thread

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