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().