Dynamic formula with multiple values connected by OR

Sylvia Kay · 2023-12-05T02:47:29+00:00

There was an error rendering this rich post.

I am using this formula in the green cell:

=COUNTIFS({Product Development Backlog v1.0 - Status Column}, $Input@row, {Product Development Backlog v1.0 Initiative Column}, CONTAINS($[Output (Interpretation)]$2, @cell), {Product Development Backlog v1.0 Story}, CONTAINS([Output (Interpretation)]$14, @cell))

I would like to change it, but do not know how:

I like to change this part of the formula, so that it does not look for just 1 term, but multiple terms using "OR": ... {Product Development Backlog v1.0 Initiative Column}, CONTAINS($[Output (Interpretation)]$2, @cell ...

As I have to now, the formula counts every line in Product Development Backlog v1.0 that fulfills these criteria:

The reference Column 'Product Development Backlog v1.0 - Status Column' contains the term that is in the red field to the very left of the formula (which in Line 16 would be 'IN PROGRESS') AND
The value from the red field in line 2 (which contains the word HCorpo) is found in the reference Column 'Product Development Backlog v1.0 Initiative Column'
AND
The reference column 'Product Development Backlog v1.0 Story' contains the value from the first red field in line 14 (which contains the word Story)

I would like to change only the second criteria (HCorpo), so that I can combine multiple Initiatives. In field 'Output (Interpretation)'@line 3 I have the term 'Hcorpo1' and In field 'Output (Interpretation)'@line 4 I have the term 'Hcorpo2'

Now I would like to count:

The value from either the red field in line 2 (which contains the word HCorpo) OR the red field in line 3 (which contains the word Hcorpo1) OR the red field in line 3 (which contains the word Hcorpo2) is found in the reference Column 'Product Development Backlog v1.0 Initiative Column'

Thank you so much for helping solve my puzzle :-)

Sylvia

Find more posts tagged with

Formulas

Accepted answers

Sylvia Kay

@Jason Albrecht Pure magic! thank you so very much for your help with this. Works as intended. Starting to like formulas 😂

Jason Albrecht

Hello @Sylvia Kay

Would the following work? replace CONTAINS($[Output (Interpretation)]$2, @cell) with...

OR(CONTAINS($[Output (Interpretation)]$2, @cell), CONTAINS($[Output (Interpretation)]$3, @cell), CONTAINS($[Output (Interpretation)]$4, @cell)

I took some inspiration from these other community answers and information on functions:

COUNTIF WITH CONTAINS/ AND/OR — Smartsheet Community

if / or / contains — Smartsheet Community

Nested IF/OR Contains Function — Smartsheet Community

CONTAINS Function | Smartsheet Learning Center

OR Function | Smartsheet Learning Center

All comments

Jason Albrecht

Glad I could help, and yes, formulas are amazing when they work.

Your detailed question and image helped a lot.

All the best.

Sylvia Kay

@Jason Albrecht Pure magic! thank you so very much for your help with this. Works as intended. Starting to like formulas 😂

Jason Albrecht

Hello @Sylvia Kay

Would the following work? replace CONTAINS($[Output (Interpretation)]$2, @cell) with...

OR(CONTAINS($[Output (Interpretation)]$2, @cell), CONTAINS($[Output (Interpretation)]$3, @cell), CONTAINS($[Output (Interpretation)]$4, @cell)

I took some inspiration from these other community answers and information on functions:

COUNTIF WITH CONTAINS/ AND/OR — Smartsheet Community

if / or / contains — Smartsheet Community

Nested IF/OR Contains Function — Smartsheet Community

CONTAINS Function | Smartsheet Learning Center

OR Function | Smartsheet Learning Center

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.