count consecutive cells matching string - start after 3 - ignore blanks between

[godlev]

i want find formula to count consecutive cells matching string (e.g. 1) but the counting of the consecutive cells to start after the 3rd consecutive entry
so if you have on a single row 5 consecutive times number 1 - the result should be 2 because the counting should start after 3rd entry
if you have 8 consecutive entries of number 1 but somewhere between them there is blank cell that should be also considered as single consecutive entry and the result should be 5


as shown on a row 9 - there are 9 consecutive times the number 1
the result is 6 because it should count only after the 3rd consecutive number 1

on row 10 is counting again only after the 3rd consecutive number 1
there are 2 consecutive entries with another number between them and it should be considered as two different consecutive entries of 1 - so the result should be 2

additionally on row number 11 are shown 2 consecutive entries with blank cell between them should be also considered as 1 consecutive entry

the numbers which the formula should count are shown below in red

[TABLE="class: grid, width: 290, align: center"]
<tbody>[TR]
[TD="align: center"]A1
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="colspan: 9, align: center"]Entries
[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[godlev]

any suggestions i got stuck here

=COUNTIFS(B3:G3,1,C3:H3,1,D3:I3,1,E3:J3,1)

 

[godlev]

bump

 

[Eric W]

Welcome to the forum.

How about:

K3: =SUM(IFERROR(SQRT(FREQUENCY(IF(B3:J3=1,COLUMN(B3:J3)),IF((B3:J3<>1)*(B3:J3<>""),COLUMN(B3:J3)))-3)^2,""))

confirmed with Control+Shift+Enter. The 1 in red (2 places) is the value you're looking for.

 

[godlev]

thank you Eric
this solves my issue and it will save me alot of manual work and manual mistakes

 

[godlev]

i just realized there is another requirement that i have to include
imagine there are 2 sheets sh1 and sh2
the counting should start from sh1 cells A1:A3
if there is 1 to continue the counting in sh2 where the whole data is as shown above

 

[godlev]

bump!