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
-
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
-
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
-
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!
-
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?
-
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.
-
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
-
Great news, so glad it worked!
Thanks for the update. Hope you have a great day too!
BRgds,
-Ray
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!