COUNTIF Wildcard Substitution
I am trying to use contains and it isn't working. I am trying to look up from a row in one sheet to count from another sheet here is the formula: =COUNTIF({SMARTRECRUITER, Merkle Range 3}, Recruiter@row)
This works but is a value of 7 and not 8. It is missing some as the value in "Recruiter@row" is in the range but is with other names as well. Recuriter@row is a name of a person but in my range it might say John Smith, Jared Holton. I tried doing this:
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, CONTAINS(Recruiter@row, {SMARTRECRUITER, Merkle Range 3}))
But that brings a value of 0. What can I use in place of the wildcard?
Best Answer

Right. Replace the range in the CONTAINS function with "@cell" exactly how I had it in my first formula.
Answers

Try an "@cell" reference instead.
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, CONTAINS(Recruiter@row, @cell))
If you are referencing a contact type column though, this still will not work because of the CONTAINS function (it doesn't like contacts). We would need to replace it with a HAS function like so...
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, HAS(@cell, Recruiter@row))

Hi Paul.
This still isn't working, it isn't a contact type column it is just text. However, that isn't working. Below are three that I tried and came back with 0.
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, CONTAINS(Recruiter@row, Recruiter1:Recruiter1))
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, CONTAINS(Recruiter@row, {SMARTRECRUITER, Merkle Recruiters1:106}))
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, CONTAINS(Recruiter1:Recruiter1, {SMARTRECRUITER, Merkle Range 3}))
SMARTRECRUITER is a separate sheet that contains all of the data that I need to count, it has a column "Recruiters" that has a list of names. In my current sheet that I am counting in I have a column called "Recruiter" where I have all of the Recruiter's names written out and am trying to get a count of how many are listed in the SMARTReCRUITER sheet. Hopefully that helps...
I need this but with a wildcard such as *:
=COUNTIF({SMARTRECRUITER, Merkle Range 3}, *Recruiter@row*)

Right. Replace the range in the CONTAINS function with "@cell" exactly how I had it in my first formula.

You literally meant @cell not @ the cell, got it and it worked! Thank you.

Exactly. Glad you were able to get it working and happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!