SUMIF multiple columns and checkboxes
I am trying to set a formula that will do this:
Balance Remaining = Total Amount  Draw 1 Amount  Draw 2 Amount  Draw 3 Amount (only if the D1, D2, D3 checkboxes are checked for each)
Best Answer

Smartsheet allows you to do math on the results of formulas within a single cell. From your logic, you only want a value subtracted from the total amount if its box is checked. So let's tell Smartsheet that:
=[Total Amount]@row  IF([D1 Rcvd]@row, [Draw 1 Amount]@row)  IF([D2 Rcvd]@row, [Draw 2 Amount]@row)  IF([D3 Rcvd]@row, [Draw 3 Amount]@row)
In the row in your screen shot, the result would be $40,000.00 ($60,000.00  $20,000.00  null  null)
Why this works:
When using IF statements with checkboxes, using IF([Column Name]@row tells Smartsheet to see if the cell is checked (as opposed to IF(NOT([Column Name]@row, which tells it see if the cell is not checked.)
Since we're dealing with numeric values that we want to stay numeric values, by not giving any false condition in the IF statement, a false condition (unchecked box) results in literally nothing. Using a false condition like "" for a blank cell tells Smartsheet to convert the cell to text, which breaks the arithmetic since Smartsheet can't do math on text, only numbers.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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

Smartsheet allows you to do math on the results of formulas within a single cell. From your logic, you only want a value subtracted from the total amount if its box is checked. So let's tell Smartsheet that:
=[Total Amount]@row  IF([D1 Rcvd]@row, [Draw 1 Amount]@row)  IF([D2 Rcvd]@row, [Draw 2 Amount]@row)  IF([D3 Rcvd]@row, [Draw 3 Amount]@row)
In the row in your screen shot, the result would be $40,000.00 ($60,000.00  $20,000.00  null  null)
Why this works:
When using IF statements with checkboxes, using IF([Column Name]@row tells Smartsheet to see if the cell is checked (as opposed to IF(NOT([Column Name]@row, which tells it see if the cell is not checked.)
Since we're dealing with numeric values that we want to stay numeric values, by not giving any false condition in the IF statement, a false condition (unchecked box) results in literally nothing. Using a false condition like "" for a blank cell tells Smartsheet to convert the cell to text, which breaks the arithmetic since Smartsheet can't do math on text, only numbers.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!

Perfect. Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!