How to subtract checkboxed values from a total

Question

I have a column of values with the value sum at the top. The column to the right of this column has checkboxes adjacent to each value (except sum). I would like to fix a formula I have to ensure the sum does not include all values that have their respective checkboxes checked.

I know this is wrong because it only includes one cell reference instead of all cells that are checked.

=(SUM(S3:S43)-(IF(T3=TRUE,-S3)))

This satisfies only T3, but not the entire column of values IF their adjacent checkboxes are checked. What needs to be adjusted in the formula?

user11982798 user11982798 · Accepted Answer · 2019-12-16T00:10:58

Try:

= ArrayFormula
  (  
    SUM(S3:S43) - 
    IF
      ( t3:offset(t3,
        MAX(
             if
               ( isblank(S3:S43), 
                 0, 
                 row(S3:S43)
               )
            ) - ROW(S3), 0) = TRUE , - S3:S43
      )
  )

How to subtract checkboxed values from a total

1 Answers