Multiple Criteria with Different Value Outcomes

JenWhiteCO

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.

brianschmidt

Your IF statements need to be nested. Is your EC Role column a multi-select 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".

brianschmidt

Your IF statements need to be nested. Is your EC Role column a multi-select 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".

JenWhiteCO

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

brianschmidt

@JenWhiteCO Glad it worked!:)