google-sheetsgoogle-sheets-formula

Sum the continuity in columns of Google sheet

Sheets -> Google Sheets (not PC Excel)

enter image description here

I want the answer to columns C and D as 3 (separately under their columns) due to their continuity. What would be its formula?

I mean, as long as the data is the same in a column, it is counted as one entry. Gaps could be longer, but there must be one cell to differentiate/break continuity

EDIT: Columns C & D are just examples, so need answer to column C separately and D separately.

Solution

  • You need COUNTIFS(). Try-

    =COUNTIFS(C:C,TRUE,D:D,TRUE)

    Edit: After comments by OP. SCAN() function may work. Try-

    =MAX(SCAN(0,C1:C,LAMBDA(a,x,IF(AND(x=TRUE,x<>OFFSET(x,1,0)),a+1,a+0))))

    for D Column-

    =MAX(SCAN(0,D1:D,LAMBDA(a,x,IF(AND(x=TRUE,x<>OFFSET(x,1,0)),a+1,a+0))))