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
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…