Dynamic formula with multiple values connected by OR

Sylvia Kay

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

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

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

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

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

Your detailed question and image helped a lot.

All the best.