Smartsheet formulas with multiple columns

Wesley Gammill · 04/20/17

We're using Smartsheet for some change management, and I'm new to both Smartsheet and spreadsheet formulas.

What I’d like to do is have a formula looks at a column that indicates the type of change (Major, Minor, Emergency, etc.) and then looks at a number of check boxes and adds an approval in a separate column.

So, for instance, if it’s an emergency change, then only one approval is necessary. There are 6 people on the CAB, so I’d want it setup so if the change type is emergency, then as long as at least one box is checked, the change is approved. If it’s a Major change, then 3 or more check boxes would need to be checked.

If I'm thinking correctly, it would work like this: change type column determines number of check boxes necessary, a formula counts the number of boxes checked (0-6) and then determines whether a sufficient number of boxes have been checked to change a separate column to "approved."

SmSulli · 04/21/17

I believe this is what you're asking for. An "Approval" column checks how many Checkboxes are checked with the type of change. I've used a countif to count the number of sign checkboxes.

Counter column counts the number of sign checkboxes.

Counter Column Formula:

=COUNTIF([Sign 1]2:[Sign 3]2, 1)

Nested if statement to compare the number of sign checkboxes with the counter.

Approval Column Formula:

=IF(AND([Change Type]2 = "Emergency", Counter2 >= 1), "Approved", IF(AND([Change Type]2 = "Minor", Counter2 >= 2), "Approved", IF(AND([Change Type]2 = "Major", Counter2 >= 3), "Approved", "Not")))

I'd also recommend using a dropdown box in the change type to make sure an odd type, or typo doesn't mess things up.

Good Luck

Welcome to the Smartsheet Forum Archives

Smartsheet formulas with multiple columns

Comments

Categories