Adding value to a cell from a checkbox

Options

how can I do this with multiple checkboxes?

=IF(checkbox24 = 1, value24 + 50, value24)

I want each box that is checked to add the cost of that item to my total.

Right now I am using the below so if no box is checked it defaults to the $25k number.

=IF([Carpet Removal]@row = 1, 25000 + 20000, 25000)

Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    Options

    @KWilliford Is each Checkbox on its own row? If so, you could use SUMIF or SUMIFS

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

    Hi @KWilliford

    I hope you're well and safe!

    If I understand your need correctly, you could add something like this for the column where you total the amount from your formula. (change the column name "Total" to yours)

    =SUMIF(Total:Total, <>"")

    Did 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.

  • KWilliford
    KWilliford ✭✭✭
    Options

    I have 10 checkboxes in the same row, that are for different column headers. If the box is checked, I would like my total line to add the amount I have set for that line to my total. I also was the total to always include a standard $25k amount. I can get the one box to add, and be $25k if unchecked but don't know how to do multiple boxes... Capital improvements is my total to the left of the checkboxes.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @KWilliford

    I would personally use the COUNTIF function to count how many boxes are checked ( = 1 ) and then multiply that count by 25,000:

    =COUNTIF([Carpet Removal]:[Roof Replace], 1) * 25000

    Let us know if that makes sense and works for you!

    Cheers,

    Genevieve

  • KWilliford
    KWilliford ✭✭✭
    Options

    I am getting a strange count, how do I assign numbers for the check boxes?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @KWilliford

    The COUNTIF formula counts how many boxes are checked or = 1

    =COUNTIF([Carpet Removal]:[Roof Replace], 1)

    For your image, this should return 4, since you have 4 boxes checked.

    Then you can multiply that by whatever value you want! I may have misinterpreted what you wanted to do, but in my formula above I multiplied * by 25,000, so each checked box is worth 25,000.

    =COUNTIF([Carpet Removal]:[Roof Replace], 1) * 25000

    or

    =4 * 25000

    Does that make sense? Try the formula without the multiplication to ensure it's counting the checkboxes correctly. If it's not working, it would be helpful to see a screen capture.

    Thanks!

    Genevieve

  • KWilliford
    KWilliford ✭✭✭
    Options

    i see, for mine each column is a different amount so I can't just multiply it all by the same number. So I couldn't do a sum I would have to do individuals and then sum them up?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @KWilliford

    Ah, thank you for clarifying. Yes, in this case you would need to assign your individual values to each checkbox so the formula knows what each box is worth.

    For example:

    =IF([Carpet Removal]@row = 1, 25000, 0) + IF([Roof Replace]@row = 1, 5000, 0)

    adding separate IF statements together until you have one value per checkbox.

    Cheers,

    Genevieve

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @KWilliford If you wanted to make it more dynamic:

    Create another sheet that holds the values of each service (2 columns, one with service name and one with value). The order of services in this sheet should have the same order as your columns here.

    Then your formula here would be:

    =SUMIFS({Service Sheet - Values}, [Carpet Removal]@row:[Roof Replace]@row, 1)

    {Service Sheet - Values} - Cross sheet reference to your service sheet select the values only

  • KWilliford
    KWilliford ✭✭✭
    Options

    Thank you for the responses. I will do the longer formula for now but will look at adding a service sheet in the future that can be maintained by our facilities team. Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!