Is it possible to create a column formula that converts "true" checkbox to value?

pris
pris ✭✭✭✭
edited 12/08/23 in Formulas and Functions

I have a sheet that when a column is checked, I would like it to become a value that can be summed. Is it possible? I have a sheet that people can select if they attended several trainings by checking the box of the trainings they attended. I then want to add together the total hours for the training by giving the checkbox a value and summing the columns. If this is not possible, I am open to structuring my sheet differently. I set up an automation to change the value but it cannot be summed because it is a checkbox field. Here is a link to the sheet https://app.smartsheet.com/b/publish?EQBCT=aa235c24a84a4afe92a2c619e094c2f2

Thank you for your help!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @pris

    I hope you're well and safe!

    You could add a so-called helper column with the value and use that for the calculation.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • pris
    pris ✭✭✭✭

    @Andrée Starå I could do this but I was trying to avoid

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @pris

    Unfortunately, you need something indicating that it's a parent row. Do you have anything else you could use, like something in the name or similar?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • pris
    pris ✭✭✭✭

    @Andrée Starå Each training has a unique name and a unique number of hours. Some trainings also have a cultural component. Would structuring differently help. I can not do checkboxes. I can do dropdown, if they select that training, have a value associated with it and have an automation that changes the text to value? But if they are cultural have it change in a different cell? My end goal is to have a clear number of hours attended and how much of those hours qualify as cultural.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!