Return cell value if all criteria is met

Options

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 ✓
    Options

    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

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hi @Miss_Priss

    If I'm reading the formula correctly, it looks like you've already accounted for "Canada" in a previous IF statement, suggesting that if all previous IF statements are true, the false statement can simply be GEO@row ?

    Or have I misunderstood the outcome you're looking to achieve?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Miss_Priss
    Options

    @Jason Albrecht

    Yes I think we're on the same page. Basically the outcome would be to return all other fields in that cell but omitting "Canada" as a response. I know there's a conflict along my string somewhere I just can't quite get it right.

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

    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

  • Miss_Priss
    Options

    @Jason Albrecht Thanks for cracking this for me! It's still a struggle transitioning my thinking from excel to Smartsheet.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Glad I could help and thank you for the feedback.

    From experience the transition gets easier over time.

    Continue to reach out to community if ever in need.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!