Formula assistance

I am trying to create a formula for one column titled "Stats", and it is based of 4 other columns. What I need Stats to reflect is:

0 (zero) if column titled "Facilitator Name" is populated with a (applicant) name

1 if column titled "Application Status " is populated with "Accepted"

2 if column titled "Facilitator Status - Certificate Completed" is populated with "Approved"

3 if column titled "Pause New Grp Reg Reminder" is populated with a check mark

I have tried several different IFS options but cannot find a solution.

Thanks for any help!

Best Answer

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 03/01/23 Answer ✓

    Char,

    Looking at the formula I provided and the columns names you have, I now see I didn't create the same exact named columns as you. Here is the formula updated to better match your column names. I used "Facilitator Status" when it should have been "Facilitator Status - Certificate Completed". That would have made it UNPARSEABLE. I've retested, and hopefully this edited one will do the trick.

    =IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status - Certificate Completed]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

    I personally wouldn't separate the columns.

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 03/01/23

    Hi @Char Norman,

    I would use nested IF statements, and I would do them in reverse order of what you have listed. Reason being is that once a statement is true the IF cycle is broken out of and the corresponding action is taken.

    If you write it in the order you have it, every row with a Facilitator Name populated will get a 0, even any of the other items are true.

    I believe this will do it:

    =IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

    Here's from my testing:


    Hope that helps!

    All the best,

    -Ray

  • Char Norman
    Char Norman ✭✭✭✭

    Hi Ray,

    I copied your formula into my Result cell but received an #UNPARSEABLE error.

    Looking at the formula I tried to break it down to see if I could determine where the error was originating. I used the Pause New Grp Reg Reminder section but still received the same error. When I altered the formula, my logical expression worked when I entered this piece of the formula =IF([Pause New Grp Reg Reminder]:[Pause New Grp Reg Reminder], but it errored again when I added any values.

    I wonder if I should just create 3 separate Results columns for each column.

    Thanks for your help!

  • Char Norman
    Char Norman ✭✭✭✭

    Ray,

    By separating the column results I wonder if I can then pull the data into one report that will total the amount in the columns for me.

    Thoughts?

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 03/01/23 Answer ✓

    Char,

    Looking at the formula I provided and the columns names you have, I now see I didn't create the same exact named columns as you. Here is the formula updated to better match your column names. I used "Facilitator Status" when it should have been "Facilitator Status - Certificate Completed". That would have made it UNPARSEABLE. I've retested, and hopefully this edited one will do the trick.

    =IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status - Certificate Completed]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

    I personally wouldn't separate the columns.

  • Char Norman
    Char Norman ✭✭✭✭

    Hi Ray,

    THAT WORKED GREAT! Thank you. I didn't notice the name either, so thanks again for that great eye.

    Certainly appreciate your help!

    Have a great day,

    Char

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    Great news, so glad it worked!

    Thanks for the update. Hope you have a great day too!

    BRgds,

    -Ray

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!