Subtracting checked rows from a total
I'm trying to figure out how to create a formula that would subtract checked rows from a total. For example in the photo I have the 4 items on the right summed in the upper left to $16,000. Every time i check a box and get a strikethrough, I'd like the total to adjust to the remaining items. Thanks!
Best Answer
-
Sure.
The SUMIFS function gives you the ability to add multiple criteria to the SUM function, in order to include or exclude certain cells based on their values or the values of other columns in the same row.
The syntax is:
SUMIFS( range, criterion_range1, criterion1, criterion_range2, criterion2... etc)
I'm going to assume your column with the dollar values is called "Amount", and the column with the checkboxes is called "Checkbox" and that we're dealing in your example with rows 12-15.
=SUMIFS(Amount12:Amount15, Checkbox12:Checkbox15, 0)
Checkboxes have a value of 1 if checked, 0 if unchecked. So this formula says count the amounts where the checkbox is not checked.
Now if you're using Parent/Child rows (where your row with $16,000.00 in it is the parent row of the four indented child rows beneath it, you could create a formula to use on every parent row without having to change the row numbers each time:
=SUMIFS(CHILDREN(Amount@row), CHILDREN(Checkbox@row), 0)
This sheet has examples of most formulas:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
What formula are you using to get your SUM total?
You can change this to a SUMIFS, with one of the criteria being that the checkbox column is unchecked. Then when a checkbox gets checked, it would no longer be eligible to be counted in the sum, and the sum would reduce by that row's amount.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I'm using a simple formula =SUM(range)
I am fairly new to Smartsheets so don't know where to start creating more advanced formulas. Any step by step guidance would be greatly appreciated!
-
Sure.
The SUMIFS function gives you the ability to add multiple criteria to the SUM function, in order to include or exclude certain cells based on their values or the values of other columns in the same row.
The syntax is:
SUMIFS( range, criterion_range1, criterion1, criterion_range2, criterion2... etc)
I'm going to assume your column with the dollar values is called "Amount", and the column with the checkboxes is called "Checkbox" and that we're dealing in your example with rows 12-15.
=SUMIFS(Amount12:Amount15, Checkbox12:Checkbox15, 0)
Checkboxes have a value of 1 if checked, 0 if unchecked. So this formula says count the amounts where the checkbox is not checked.
Now if you're using Parent/Child rows (where your row with $16,000.00 in it is the parent row of the four indented child rows beneath it, you could create a formula to use on every parent row without having to change the row numbers each time:
=SUMIFS(CHILDREN(Amount@row), CHILDREN(Checkbox@row), 0)
This sheet has examples of most formulas:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks so much!
Help Article Resources
Categories
Check out the Formula Handbook template!
