+ Reply to Thread
Results 1 to 11 of 11

Needing to search for an exact match on a sheet within a formula (COUNTIF help)

  1. #1
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Okay, first a bit of context... I work in the airlines, and I've designed a spreadsheet to help me track flight delays and make my reports at the end of the night easier. I have a table on a separate page that auto-refreshes based on a website. I have a COUNTIF formula that searches a column (column W) for a particular string of text (in this case, "CL" and "LTCL"). However, in some cases the column will include things like "CNCL" or "PCCL", and my counters go up because it includes "CL." Here is my formula:

    =SUM(COUNTIFS(SSD!W:W, {"*CL*","*LTCL*"}))

    Can I modify this so that it only finds EXACTLY CL or LTCL and counts? I want to exclude any other codes that include a rogue "CL". (for example the word "CLOSED" in that column causes it to count up one.)
    All my google searches returned answers for INDEX and MATCH functions, and that's not what I'm looking for.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Hi, welcome to the forum

    Why not just remove the * from CL?

    =SUM(COUNTIFS(SSD!W:W, {"CL","*LTCL*"}))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    I tried that at first, and it always returned 0 for some reason. It wouldn't count at all unless I had the asterisks in there.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    It worked for me?

    H
    I
    1
    CL
    4
    2
    Closed
    3
    clasp
    4
    LTCL
    5
    CL
    6
    Closed
    7
    clasp
    8
    LTCL

    =SUM(COUNTIF($H$1:$H$8,{"CL","LTCL"}))

    Check to make sure that CL cells dont have leading/trailing spaces

  5. #5
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Huh, that's super strange. Let me try again and see what happens.

  6. #6
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Ah, that must have been it. I modified the formula like this:

    =SUM(COUNTIF(SSD!W:W, {"CH ","LTCH "})) with trailing spaces. The website must populate the data that way for some reason. Seems really odd! So then the * functions as a wildcard then, correct?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    So then the * functions as a wildcard then, correct?
    Correct. * will match any (and any amount of) characters, and ? will match any 1 character

  8. #8
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Aha! Good to know. Would the ? Come before or after the character? Or it'll match one in either direction based on where you put it?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    abc?efg
    will match
    abcdefg
    abcaefg
    abczefg
    etc

  10. #10
    Registered User
    Join Date
    02-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    12

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    Oh I see. Thank you for the help!! I really appreciate it, I've been struggling with this for quite awhile.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Needing to search for an exact match on a sheet within a formula (COUNTIF help)

    No problem, Im happy I was able to help

+ 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] Match Exact formula and Search list with results
    By newbie4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-29-2015, 08:20 PM
  2. [SOLVED] SEARCH and EXACT match
    By bibu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2014, 06:07 PM
  3. Replies: 5
    Last Post: 07-26-2012, 09:51 AM
  4. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  5. Replies: 2
    Last Post: 01-28-2012, 05:26 PM
  6. COUNTIF Not Exact Match
    By Dbeethekidd in forum Excel General
    Replies: 1
    Last Post: 01-12-2012, 07:19 AM
  7. COUNTIF not exact match
    By Jogier505 in forum Excel General
    Replies: 2
    Last Post: 06-02-2010, 08:42 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