Using COUNIFS to find a partial text match in 1 cell + if another cell is filled/blank/specific text

Morrison Bent

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;

1. 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")
2. 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

Paul Newcome

You would want to incorporate a CONTAINS function.

=COUNTIFS({Column 1}, CONTAINS("X Text", @cell), {Column 2}, CONTAINS("Y Text", @cell))