Cross Sheet Formula COUNTIFS Help
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.
Best Answers
-
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)
-
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
Answers
-
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)
-
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
-
Thank you very much. This has worked perfectly and will make many peoples days.
-
Glad to hear @Simon Bamford. It is certainly rewarding when someone's problem is solved and their day has been made.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!