Return cell value if all criteria is met

Hello all!

After several hours and scouring the community archives, I am just not sure how to solve the last part of my formula.

The scenario is that I have a column with a formula to create "teams" based on what criteria is met in the rest of the sheet. Some lines on the sheet meet more than one criteria, but I've laid the formula out in a way that seems to be working for this purpose.

The only part that isn't working is the last bit of the formula below. Basically, the column I'm referencing includes a value that I would like to omit. If a cell in this column does not contain "Canada" then I want the formula to retrieve the value of the cell as a response.

Right now I'm getting an #INVALID COLUMN ERROR which I think is due to the column I'm referencing having the value I'm trying to avoid. Is this formula possible, or would I need a helper column to resolve this?

=IF([Project Activate]@row = 1, "Project Activate", IF(PSA@row = 1, "PSA Team", IF(GEO@row = "Canada", "Canada", IF(PSA@row = 0, [Project Activate]@row = 0, NOT(CONTAINS(GEO@row = "Canada", GEO@row))))))

Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi @Miss_Priss

    I believe this is what you're after?

    =IF([Project Activate]@row = 1, "Project Activate", IF(PSA@row = 1, "PSA Team", IF(GEO@row = "Canada", "Canada", IF(AND(PSA@row = 0, [Project Activate]@row = 0, GEO@row <> "Canada"), GEO@row, ""))))


    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!