Cross Sheet Formula COUNTIFS Help

Simon Bamford

Hi,

I have a metric sheet that links people with the status of a project.

=COUNTIFS({Corporate Objectives 2022 Range 1}, [Assigned Started]@row, {Copy of Corporate Objectives 2022 Range 1}, Status1)

[Assigned Started] is the person assigned the project.

[Status1] is the project status.

That formula works no problem when there is only one person but some projects have 2 people in the assigned on the main sheet. So it cannot read these.

Does anyone know how to modify the formula so that it can read either name?

Thank you for the help.

Paul Newcome

You would need to use a HAS function.

=COUNTIFS({Corporate Objectives 2022 Range 1}, HAS(@cell, [Assigned Started]@row), {Copy of Corporate Objectives 2022 Range 1}, Status1)

Jake Gustafson

Try this:

=COUNTIFS({Corporate Objectives 2022 Range 1}, HAS(@cell, [Assigned Started]@row), {Copy of Corporate Objectives 2022 Range 1}, Status1)

I found this Community post that was helpful. I was trying to use the CONTAINS function as I've used that in the past with multi-select columns, but the HAS function seems to be getting the job done.

Formula help - count of a value from a multi-select dropdown field — Smartsheet Community

Paul Newcome

You would need to use a HAS function.

=COUNTIFS({Corporate Objectives 2022 Range 1}, HAS(@cell, [Assigned Started]@row), {Copy of Corporate Objectives 2022 Range 1}, Status1)

Jake Gustafson

Try this:

=COUNTIFS({Corporate Objectives 2022 Range 1}, HAS(@cell, [Assigned Started]@row), {Copy of Corporate Objectives 2022 Range 1}, Status1)

I found this Community post that was helpful. I was trying to use the CONTAINS function as I've used that in the past with multi-select columns, but the HAS function seems to be getting the job done.

Formula help - count of a value from a multi-select dropdown field — Smartsheet Community

Simon Bamford

Thank you very much. This has worked perfectly and will make many peoples days.

Jake Gustafson

Glad to hear @Simon Bamford. It is certainly rewarding when someone's problem is solved and their day has been made.

Paul Newcome

@Simon Bamford Happy to help. 👍️

@Jake Gustafson One thing to keep in mind... The CONTAINS function doesn't register contacts in a contact type column, but HAS and FIND both do.