Subtracting checked rows from a total

mike_factor

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318334#Comment_318334

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)

https://help.smartsheet.com/functions

https://help.smartsheet.com/articles/2476176-formula-error-messages

This sheet has examples of most formulas:

https://app.smartsheet.com/sheets/mrHG6RhFpp6wQMj4p3h9jgQRWW3Xjhxjr53VJ2v1?view=grid

Jeff Reisman

https://community.smartsheet.com/discussion/87571/subtracting-checked-rows-from-a-total

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.

mike_factor

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318334#Comment_318334

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)

https://help.smartsheet.com/functions

https://help.smartsheet.com/articles/2476176-formula-error-messages

This sheet has examples of most formulas:

https://app.smartsheet.com/sheets/mrHG6RhFpp6wQMj4p3h9jgQRWW3Xjhxjr53VJ2v1?view=grid

mike_factor

Thanks so much!