Assigning Overflow Credits

Options
✭✭

I am trying to automate educational credits. All users need a total of 8 hours of education. They HAVE to do 4 hours of neurotrauma hours. The remaining 4 hours can be general trauma or neurotrauma.

Person 1: Completes 4 neurotrauma hours and 4 general trauma hours. Complete

Person 2: Completes 6 neurotrauma hours and 2 general trauma hours. Complete

Person 3: Completes 8 neurotrauma hours and 0 general trauma hours. Complete

Person 4: Completes 2 neurotrauma hours and 6 general trauma hours. Incomplete. Needs 2 more NT hours

I'm thinking of any neurotrauma hours over 4 to be "overflow" to general trauma.

Any recommendations? Thanks.

• ✭✭✭✭✭✭
Options

Lets try a different approach

If Neuro is greater than or equal to 4 and grand total is greater than or equal to 8, then check the complete box?

=IF(AND([Neuro Trauma: Completed]@row>= 4, [Neuro Trauma: Completed]@row + [General Trauma: Completed]@row>= 8), 1)

• ✭✭✭✭✭✭
Options

How are the cells in your screenshot currently being populated?

• ✭✭
Options

Staff submits requests for approval of credits. The awarded totals populate in an employee reference sheet pulled from the request sheet.

=SUMIF({Email}, [Email Address]1, {Neuro Trauma Total})

=SUMIF({Email}, [Email Address]1, {General Trauma Total})

• ✭✭✭✭✭✭
Options

Lets try a different approach

If Neuro is greater than or equal to 4 and grand total is greater than or equal to 8, then check the complete box?

=IF(AND([Neuro Trauma: Completed]@row>= 4, [Neuro Trauma: Completed]@row + [General Trauma: Completed]@row>= 8), 1)

• ✭✭
Options

Thank you, that worked perfectly.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!