Dynamic formula with multiple values connected by OR

Options

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

SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

PLATFORM ENGINEERING & TECHNOLOGY TEAM

AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

AMEXGLOBALBUSINESSTRAVEL.COM

follow us on twitter: @AmexGBT

follow us on instagram: @AmexGBT

Tags:

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!