IF / CONTAINS using a range

Have an issue I thought I had resolved, but ...

I have a sheet that has two relevant data ranges:

Range 1 = a single column that specifies a Content Type for each row item. There are 7 possible Content Types: Data Sheet, Ordering Guide, BDM, TDM, Demo, Learning Map, Proposal

Range 2 = 15 columns that contain a Product Family for each row. Some rows have multiple Product Families, and each Product Family is contained in its own cell (thus why the range spans 15 columns)

I have a separate sheet that tracks whether I have one of each Content Type for every Product ID. To assess, I had been using this formula:

=IF(AND(CONTAINS([Product Family]@row, {Range 2}), CONTAINS("Data Sheets", {Range 1})), "True", "False")

I realize now, though, that it isn't quite doing what I want, because if the formula finds both the Product Family in Range 1 and the Content Type in Range 2, it is coming back "TRUE" even if the Content Type isn't appearing on the same row as the Product Family.

Any suggestions on how I might modify this? I tried this:

=IF(AND(CONTAINS([Product Family]@row, {Range 2}), {Range 1}="Data Sheets")), "True", "False"), but I get an INVALID OPERATION error.

Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓

    Figured it out using a formula @Paul Newcome gave me before:

    =IF(COUNTIFS({range 1}, "content type name", {range 2}, CONTAINS([Product Family]@row, @cell))>0, "Green", "Red")

    But, Range 2 had to be converted from 15 separate columns into a single column using JOIN to concatenate the entries.

    Thanks again, Paul!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!