SUMIF multiple columns and checkboxes

CGray

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)

Jeff Reisman

@CGray

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.

Jeff Reisman

@CGray

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.

CGray

Perfect. Thank you!