COUNTIF or COUNT  partial strings of Contacts
I have the following formula:
=COUNTIFS({SECONDARY  OPCO Log Range 3}, CONTAINS("YKE", @cell))
In the BIC column I have several parties, and the ones associated with my company all have YKE/SJCCRB, YKE/SJCCSJ, YKE/SJCCKT, etc. I want the number of people in my company (my company = YKE/SJCC) that are in BIC column. See below TWO screenshots:
Here's a 2nd screen shot just further up in the same sheet:
I think the problem is it's a "contact". I don't know, but it's returning a "0".
Thanks!
Scott
Answers

Hey @Scott_Jennings
What range does {SECONDARY  OPCO Log Range 3} refer to? Is this the combined columns of Source/Type and BIC? Your screenshots indicate the criteria could be in either column. (As a good practice, consider renaming the generically named smartsheet range with the actual columns used in the range). If this is true, hopefully it wouldn't be in both as they would be double counted.
Let's start with this formula and see where we land.
=COUNTIFS({SECONDARY  OPCO Log Range 3}, FIND("YKE", @cell) > 0)
The FIND function returns a position number in a string, not a true or false, thus the criteria is looking for a string position greater than zero (which means it found it).
Kelly

Hi Kelly,
The range is the BIC column only.
It returned a "2". I guess because there are two "YKE/SJCC" strings and the other one says "Scott Jennings"? I'm not sure yet (I'm a new user) how contacts come in....by the email of the person or the person's name or the designation I give them....?????
Scott

Hey Scott
Yes, if the range is only the BIC column, then only two cells have the string "YKE". Will all the contacts that show names, like yours, have the YKE in the Source/Type column? Or, possibly, does the Source/Type column designate all of your employees regardless of how they are represented in the BIC column? If this is the case, we simply swap the column used in the countifs.
If any of the above are true, we can change your range to incorporate the correct column(s) and use the same formula I provided. If this isn't true, we will have to come up with a different way to designate your employees.
Let me know and we'll see what we can do
Kelly
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 63 Global Discussions
 66 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives