Count to the first three consecutive empty columns

Question

I am having trouble mixing counting cells with a value of "1" to the first three consecutive empty cells. The following image is shown as an example:

Is it possible to do this only with Excel formulas or do I need to use different code?

Answer 1

, 2 , B: Z. AA: AC, array¹, "- -" .

array¹ A2 ,

=COUNTIF(B2:INDEX(B2:AA2, MATCH(1E+99, IF(B2:AA2="", IF(C2:AB2="", IF(D2:AC2="", 1E+99))), 0)), 1)

, .

"1" ...

, . , i.

array¹ A2 ,

=COUNTIF(B2:INDEX(B2:AA2, MATCH(1E+99, IF(B2:AA2<>1, IF(C2:AB2<>1, IF(D2:AC2<>1, 1E+99))), 0)), 1)

, . , , .

---

¹ _{Ctrl + Shift + Enter↵. , .}

Answer 2

A2, :

=COUNTA(OFFSET(B2,,,,-1+FIND("111",--(B2="")&--(C2="")&--(D2="")&--(E2="")&--(F2="")&--(G2="")&--(H2="")&--(I2="")&--(J2="")&--(K2="")&--(L2="")&--(M2="")&--(N2="")&--(O2="")&--(P2="")&--(Q2="")&--(R2="")&--(S2="")&--(T2="")&--(U2="")&--(V2="")&--(W2="")&--(X2="")&--(Y2="")&--(Z2=""))))

.

UPDATE

, . :

=COUNTA(OFFSET(B2,,,,-1+FIND("000",
 RIGHT(DEC2BIN(SUM(256,(LEN(B2:I2)>0)*2^{7,6,5,4,3,2,1,0})),8)&
 RIGHT(DEC2BIN(SUM(256,(LEN(J2:Q2)>0)*2^{7,6,5,4,3,2,1,0})),8)&
 RIGHT(DEC2BIN(SUM(256,(LEN(R2:Y2)>0)*2^{7,6,5,4,3,2,1,0})),8)&--(Z2<>"")
 )))

Ctrl + Shift + Enter.

Answer 3

, , , , - - ...

=COUNT(INDIRECT("R[0]C[1]:R[0]C[" & FIND("0",CONCATENATE(SUM(B1:D1),SUM(C1:E1), SUM(D1:F1), SUM(E1:G1), SUM(F1:H1), SUM(G1:I1), SUM(H1:J1), SUM(I1:K1), SUM(J1:L1), SUM(K1:M1), SUM(L1:N1), SUM(M1:O1), SUM(N1:P1), SUM(O1:Q1), SUM(P1:R1), SUM(Q1:S1), SUM(R1:T1), SUM(S1:U1), SUM(T1:V1), SUM(U1:W1), SUM(V1:X1), SUM(W1:Y1), SUM(X1:Z1), SUM(Y1:AA1), SUM(Z1:AB1))) & "]",FALSE))

A Z, , . , , , - .

A1: =COUNT(INDIRECT("R[0]C[3]:R[0]C[" & B1 & "]", FALSE))
B1: IF(C1 > 0,B1+ 1,B1)
C1: =COUNT(INDIRECT("R[0]C[" & B1 - 2 & "]:R[0]C[" & B1 & "]",FALSE))

, , B1, 0. reset - ..

B1: =IF(B2="",IF(C1 > 0,B1+ 1,B1),0)

VBA, , , . - ...

Function CountBeforeBlanks(R As Range)
    For i = 1 To R.Count
        CountBeforeBlanks = CountBeforeBlanks + R(i)
        If Application.WorksheetFunction.Count(R(i), R(i + 1), R(i + 2)) = 0 Then
            Exit Function
        End If
    Next
End Function