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

• 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

1 Answer 1

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