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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!