CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
The DiZeeZ'D
The DiZeeZ'D

Reputation: 25

How to subtract checkboxed values from a total

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?

Upvotes: 2

Views: 417

Answers (1)

user11982798
user11982798

Reputation: 1908

Try:

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

Upvotes: 1

Related Questions