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

1. ## 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. ## 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*"}))

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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?

abc?efg
will match
abcdefg
abcaefg
abczefg
etc

10. ## 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. ## 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

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

#### 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