Using COUNIFS to find a partial text match in 1 cell + if another cell is filled/blank/specific text
Hi SmartSheet Community,
We've created a data set that is now being utilised by stakeholders, and we are trying to set up a way in which we can monitor the completion progress. The intention is to show each stakeholder their progress via our shared dashboard
Description of our desired formula;
- Count if "X text" appears in *any* cell in Column 1 in a separate report. (i.e partial text match, as the cells in Column 1 will have lots of text. We want to know all of the cells that mention "X text")
- Of these, we want to see how many have "Y text" appearing in Column 2 in the same separate report.
In our example, the specific names of the cells/criteria are;
- X text = "Australia-Faculty of Arts"
- Column 1 = column titled "Allocation for Counts" in a separate sheet called "Sem 1, 2023 EXC/ISA-Study Plan Assessment MASTER"
- This column has a multi-select dropdown, so there will be a few entries within a given cell . e.g. "Australia-Faculty of Arts, Australia-Faculty of Business, Australia-Faculty of Science".
- Y text = "Sent to student"
- Column 2 = column titled "Send outcome to student" in the same separate sheet "Sem 1, 2023 EXC/ISA-Study Plan Assessment MASTER"
When completing this, we are getting either #UNPARSEABLE errors, or it is returning incorrect values. I believe this is because the formula is only counting cells where "Australia-Faculty of Arts" is the ONLY text showing in Column 1 cells - i.e. exact matches. But we want to count all of the partial matches as well.
Putting the call out for any assistance! Has anyone got any ideas to help us out, or perhaps a different formula that would get the desired result?
Kind regards,
Morrison
Answers
-
You would want to incorporate a CONTAINS function.
=COUNTIFS({Column 1}, CONTAINS("X Text", @cell), {Column 2}, CONTAINS("Y Text", @cell))
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!