google-sheetsspreadsheet

Count value in cells above


I have a scenario like this

Hi everyone,

i have a set of checkbox, and what i'm trying to achieve here is getting a count as it can be seen in the column on the right. Basically if the checkbox value is TRUE it counts all the checkbox above itself that has a FALSE value, and then itself, then it stop when another TRUE is found

Counting if the value is false or true is simple, but i cant find a way to add this control.

Any ideas?

Thank you


Solution

  • Assuming that the checkboxes are in column A2:A, clear column B2:B and put this formula in cell B2:

    =let( 
      checkboxes, A2:A, 
      totals, scan(0, checkboxes, lambda(a, c, 
        if(c, 0, a + (c = false)) 
      )), 
      map(vstack(checkboxes, false), vstack(0, totals), lambda(c, t, 
        if(c, 1 + t, iferror(ø)) 
      )) 
    )
    

    See let(), scan(), map() and vstack().