COUNTIF or COUNT - partial strings of Contacts

Question

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/SJCC-RB, YKE/SJCC-SJ, YKE/SJCC-KT, 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

Kelly Moore · Answer

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

Scott_Jennings · Answer

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

Kelly Moore · Answer

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