Multiple Criteria with Different Value Outcomes
I'm attempting to give point value to each role based on answers in EC Role column using IF formula. This is what I have so far:
=IF(([EC Role]@row = "Coach Employed by EC Council", "3", "") + If([EC Role]@row = "Coach Serving UPK sites", "3", "") + If([EC Role]@row = "Soon to be EC Council Coach", "3", "") + If([EC Role]@row = "Early Childhood Program Director", "3", "") + If([EC Role]@row = "EQ Relate Trainer", "3", "") + If([EC Role]@row = "Other Early Childhood Initiative Coach", "3", "") + If([EC Role]@row = "Early Interventionist", "3", ""))
I have other answers that will need scores of 2 and 1 as well that have yet to be added to this formula. Not sure what I'm missing.
Best Answer

Your IF statements need to be nested. Is your EC Role column a multiselect column? If only one input is going into this column, your nested if formula will look something like this (this is just three inputs, but you can keep adding on). Note that in a nested if formula, all of your closed parentheses go on the very end of the if section of the formula as opposed to at the end of each if statement. Additionally, your "" (which I assume is that if the condition is not met) can go on the very end as the final value to generate if all previous if statements are false.
=IF([EC Role]@row = "Coach Employed by EC Council", "3", IF([EC Role]@row = "Coach Serving UPK sites", "3", If([EC Role]@row = "Soon to be EC Council Coach", "3", "")))
If you're trying to add up the value assigned to each role that is selected (assuming one line could have multiple roles selected), let me know, as that can be a little more involved. It can be done, but may involve setting up a metadata sheet to reference with all the roles and their respective point values to reference in calculating that line's total "score".
Answers

Your IF statements need to be nested. Is your EC Role column a multiselect column? If only one input is going into this column, your nested if formula will look something like this (this is just three inputs, but you can keep adding on). Note that in a nested if formula, all of your closed parentheses go on the very end of the if section of the formula as opposed to at the end of each if statement. Additionally, your "" (which I assume is that if the condition is not met) can go on the very end as the final value to generate if all previous if statements are false.
=IF([EC Role]@row = "Coach Employed by EC Council", "3", IF([EC Role]@row = "Coach Serving UPK sites", "3", If([EC Role]@row = "Soon to be EC Council Coach", "3", "")))
If you're trying to add up the value assigned to each role that is selected (assuming one line could have multiple roles selected), let me know, as that can be a little more involved. It can be done, but may involve setting up a metadata sheet to reference with all the roles and their respective point values to reference in calculating that line's total "score".

Thank you, Brian! That is EXACTLY what I needed! You're the hero of the day!

@JenWhiteCO Glad it worked!:)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!