Multiple contacts within a COUNTIF formula - please help!

Jae Wyld

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

Paul Newcome

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.

Paul Newcome

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))

Jae Wyld

Hi Paul,

That's excellent - thank you! Could you please tell me, could I extend this across three more multiple-contact 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

Paul Newcome

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.

Jae Wyld

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

Paul Newcome

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.

Jae Wyld

Thank you Paul for your help!

Paul Newcome

Happy to help! 👍️