Multiple contacts within a COUNTIF formula  please help!
I'm trying to include a list of 9 contacts as part of a COUNTIFS formula, so that if one of these 9 contacts is found in the contact column (multiple contacts enabled), it will be counted as 1 (the rest of the formula is just a status option which works fine already).
I'm looking for something like:
=COUNTIFS({Range 1  Contacts}, "Person 1 " OR "Person 2" OR "Person 3", {Range 2 Status }, "In Progress")
If you could please offer some help that would be fantastic.
Best wishes,
Jae
Best Answer

If you insert a text/number column into the source sheet you have two more options.
If the contact columns are adjacent to each other, you can use something along the lines of...
=JOIN([First Column]@row:[Last Column]@row), "/")
If they are not adjacent to each other, you can use
=[First Column]@row + "/" + [Second Column]@row + "/' + [Third Column]@row + "/" + [Fourth Column]@row
This will pull all of your columns into a single string. Then you can dragfill this down the rest of the rows and autofill should pull the formula into new rows.
Once you have that set up, can reference this joined column in the initial formula above.
Answers

Try something like this...
=COUNTIFS({Range 1 Contacts}, OR(FIND("Person 1", @cell) > 0, FIND("Person 2", @cell) > 0, FIND("Person 3", @cell) > 0, FIND("Person 4", @cell) > 0, FIND("Person 5", @cell) > 0, FIND("Person 6", @cell) > 0, FIND("Person 7", @cell) > 0, FIND("Person 8", @cell) > 0, FIND("Person 9", @cell) > 0))

Hi Paul,
That's excellent  thank you! Could you please tell me, could I extend this across three more multiplecontact columns? So that if any of these 9 people appear in any of the four columns, the formula will produce a count of 1?
Thank you so much for your help,
Best wishes,
Jae

The most accurate way to do this would be to add a column on the source sheet that joins all four of those columns together into a single string and then reference this joined column in your COUNTIFS.

Could you please advise how to do this? For this to be a viable option the formula would need to apply automatically and consistently throughout the column.
If this is not viable, could you please show how the formula would look without the use of a joined column?
Thank you again for your help
Best wishes,
Jae

If you insert a text/number column into the source sheet you have two more options.
If the contact columns are adjacent to each other, you can use something along the lines of...
=JOIN([First Column]@row:[Last Column]@row), "/")
If they are not adjacent to each other, you can use
=[First Column]@row + "/" + [Second Column]@row + "/' + [Third Column]@row + "/" + [Fourth Column]@row
This will pull all of your columns into a single string. Then you can dragfill this down the rest of the rows and autofill should pull the formula into new rows.
Once you have that set up, can reference this joined column in the initial formula above.

Thank you Paul for your help!

Happy to help! 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!