SUMIF multiple columns and checkboxes

Options

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)

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

Perfect. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!