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

Answers

  • Paul Newcome
    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
    edited 09/02/21

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!