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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!