Nested IF/INDEX/COLLECT

Options

Hello,


I’m looking to add to an existing formula. The formula below checks if the Country and Instructor Currency match and pulls in the Fee if it does match, or the Converted Fee if it doesn’t match. The formula also matches the Instructor Name, Course and Format match.

This has been working great where I have an instructor with a set fee for a class. However, some instructors are paid on a per student basis. Their fee needs to be multiplied by the number of students in the class. Currently the formula pulls in the fee for 1 student only.

I know which instructors fall into this category because the Status column will say Seat Sale. I also have a Total Students column so I know how many students are enrolled.


Anythoughts on how to add this in? If it another IF Statement?



=IFERROR(INDEX(COLLECT(IF(OR(Country@row =

"Combo", [Instructor Currency]@row = Country@row), {Fee_Instructor

Fee}, {Converted Fee_Instructor Fees}), {Instructor

Name_Instructor Fees}, Instructor@row,

{Course_Instructor Fees}, Course@row,

{Format_Instructor Fees}, Format@row), 1), 0)


Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!