I am having troubles integrating a contains formula into my Countifs formula.
I am wanting to count anytime a certain user has a certain type of follow up needed comment. The excel sheet comes from a workspace where users can select multiple options so I am looking to grab comments that contain a certain phrase. Below are my current formula, a screen shot of the metric sheet, and a filtered down version of the source sheet with the two main range columns highlighted.
=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, (CONTAINS("Education", {Retro Audit Report 2 Cred Required Comment})), {Retro Audit Report 2 Default User}, User@row)
Best Answer
-
In your CONTAINS function, instead of re-typing the entire {reference} again, you'll want to use @cell to let the function know you want to search each cell in the range for your word.
See: Create Efficient Formulas with @cell and @row
Try this formula:
=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, CONTAINS("Education", @cell), {Retro Audit Report 2 Default User}, User@row)
Note that I also removed the extra (parentheses) around the CONTAINS Function, you can list it right away. Let me know if this works for you and makes sense!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
In your CONTAINS function, instead of re-typing the entire {reference} again, you'll want to use @cell to let the function know you want to search each cell in the range for your word.
See: Create Efficient Formulas with @cell and @row
Try this formula:
=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, CONTAINS("Education", @cell), {Retro Audit Report 2 Default User}, User@row)
Note that I also removed the extra (parentheses) around the CONTAINS Function, you can list it right away. Let me know if this works for you and makes sense!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Aha! That did it. It makes sense to me now with how you laid it out. Thanks!
-
No problem! I'm glad this works for you 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!