Adding value to a cell from a checkbox
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
-
@KWilliford Is each Checkbox on its own row? If so, you could use SUMIF or SUMIFS
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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.
-
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.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I am getting a strange count, how do I assign numbers for the check boxes?
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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?
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!