SUM help

I am a newb with smartsheet and still learning. I am building a risk assessment tool and the form for the end user has check boxes. Each box gets a specific value based on risk. So anything from one box to all of them could be checked. I need the total of all boxes checked. Here's where I am stuck:

=IF([FLIGHT PROFILE]@row = "NORMAL CALL - IN COUNTY", 1, IF([FLIGHT PROFILE]@row = "NORMAL CALL - OUT OF COUNTY", 3, IF([FLIGHT PROFILE]@row = "LANDING OFFSITE - FAMILIAR", 1, IF([FLIGHT PROFILE]@row = "LANDING OFFSITE - UNFAMILIAR", 2, IF([FLIGHT PROFILE]@row = "AIRCRAFT TRAINING / EVAL", 3, IF([FLIGHT PROFILE]@row = "MAINTENANCE TEST FLIGHT", 4, IF([FLIGHT PROFILE]@row = "SPECIALIZED - TNG, K9, SWAT, SAR", 5)))))))

When individual boxes are checked, the formula works fine. I need the sum of all boxes checked and can't figure out the formula to make that happen. Any help appreciated. Thx

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Instead of nesting your IFs together, you want to "add" then together and use a HAS function.

    =IF(HAS([Flight Profile]@row, "Normal Call - In Country"), 1, 0) + IF(HAS([Flight Profile]@row, "Normal Call - Out of Country"), 3, 0) + ..........................................

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers