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
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!