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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!