Using contact on a multi-contact column as condition for countifs.

Brent Williams

I have the following formula. The range is a multi-contact column. There may be a single contact or several. The Contact8 references another cell with a single contact. I want to count any of the cells in the range that contain the contact. It may be mixed in with several others. I was trying to use the HAS or CONTAINS function but could not get the formula to resolve.

=COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range 3}, <14, {Portfolio Summary Rollup Range Status}, "Execute") / COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range Status}, "Execute")

Paul Newcome

CONTAINS will not work with Contact Type columns. You will want to use the HAS function like so...

=COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range 3}, <14, {Portfolio Summary Rollup Range Status}, "Execute") / COUNTIFS({Portfolio Summary Rollup Range BB}, HAS(@cell, Contact8), {Portfolio Summary Rollup Range Status}, "Execute")