COUNTIF Wildcard Substitution

Sarah Bystrom

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?

Paul Newcome

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

Paul Newcome

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

Sarah Bystrom

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*)

Paul Newcome

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

Sarah Bystrom

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

Paul Newcome

Exactly. Glad you were able to get it working and happy to help. 👍️