Sum the continuity in columns of Google sheet - Stack Overflow

Sheets -> Google Sheets (not PC Excel)

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.

Sheets -> Google Sheets (not PC Excel)

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.

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Feb 4 at 6:20 eagle asked Feb 4 at 5:51 eagleeagle 88333 gold badges1010 silver badges2929 bronze badges 1

• Kindly share with us what are the basis of the values of column C and D? Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link or an anonymous sample spreadsheet (using this link – EL SRY Commented Feb 4 at 6:01

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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