count of consecutive cells with same value omitting the first three

Giletant

New Member
Joined
Feb 21, 2019
Messages
4
Hello,

I have a row of 31 cells which can be blank, or contain a value of either 1 or 0. I need to count all the 1s and 0s except consecutive 0s of more than 3.
In example:
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]

So the first 3 consecutive 0s are to be counted, but further consecutive 0s are to be omitted. Blank cells, if any, are also to be omitted.

I would be very grateful for any help on the issue.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
=sumproduct(--(a1:ae1<>""))-sumproduct(--(a1:ab1=0)*(b1:ac1=0)*(c1:ad1=0)*(d1:ae1=0)*(a1:ab1<>"")*(b1:ac1<>"")*(c1:ad1<>"")*(d1:ae1<>""))
 
Upvote 0
Try this for row 1.
Code:
[COLOR="Navy"]Sub[/COLOR] MG21Feb54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Masg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]If[/COLOR] Dn.Value = 1 [COLOR="Navy"]Then[/COLOR] Msg = Msg + 1
    
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] R.Count > 3 [COLOR="Navy"]Then[/COLOR] c = c + 3
[COLOR="Navy"]Next[/COLOR] R
MsgBox "The Number of ""0'[COLOR="Green"][B]s  (3/set)"" = " & c & vbLf & "The number of ""1's"" = " & Msg[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
This seems to work, until blank cells are introduced...as the range, obviously deals with days in a month, weekends should be blank, and this breaks up the continuous counting of 0s...
 
Upvote 0
...as the range, obviously deals with days in a month, weekends should be blank, and this breaks up the continuous counting of 0s...
The explanation is far from being clear (an example or two with the desired results would help), but maybe:

=31-COUNTIF(A1:AE1,"")-SUM(COUNTIFS(A1:AB1,0,B1:AC1,{0,""},C1:AD1,{0;""},D1:AE1,0))
 
Upvote 0
The explanation is far from being clear (an example or two with the desired results would help), but maybe:

=31-COUNTIF(A1:AE1,"")-SUM(COUNTIFS(A1:AB1,0,B1:AC1,{0,""},C1:AD1,{0;""},D1:AE1,0))

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The example above should give a result of 19. The red 0s are not to be counted, as they are 4th or 5th consecutive 0s. The first 3 are to be counted.
Your formula gives a result of 20.
I hope this helps explain...
 
Upvote 0
See if this ugly formula works for you:

=SUM(COUNTIF(A1:AE1,1),COUNTIFS(A1:AC1,1,B1:AD1,0,C1:AE1,1),COUNTIFS(A1:AA1,1,B1:AB1,{0,""},C1:AC1,"",D1:AD1,{"",0},E1:AE1,1),COUNTIFS(A1:AB1,1,B1:AC1,0,C1:AD1,0,D1:AE1,{1;0})*{2;3},COUNTIFS(A1:Z1,1,B1:AA1,{0,0,""},C1:AB1,{0,"",""},D1:AC1,{"","",0},E1:AD1,{"",0,0},F1:AE1,{1;0})*{2;3})
 
Last edited:
Upvote 0
A bit shorter, but not much prettier:

=COUNT(A1:AE1)-SUM(IFERROR(EXP(LN(FREQUENCY(IF(ISNUMBER($A$1:$AE$1),IF($A$1:$AE$1=0,COLUMN($A$1:$AE$1))),IF($A$1:$AE$1=1,COLUMN($A$1:$AE$1)))-3)),0))

confirmed with Control+Shift+Enter.

The results of the 2 formulas vary in some cases (for example, if you change A1 to 0), so you may wish to check both to see which gives the results you want (if either!).
 
Upvote 0
A bit shorter, but not much prettier:

=COUNT(A1:AE1)-SUM(IFERROR(EXP(LN(FREQUENCY(IF(ISNUMBER($A$1:$AE$1),IF($A$1:$AE$1=0,COLUMN($A$1:$AE$1))),IF($A$1:$AE$1=1,COLUMN($A$1:$AE$1)))-3)),0))

confirmed with Control+Shift+Enter.

The results of the 2 formulas vary in some cases (for example, if you change A1 to 0), so you may wish to check both to see which gives the results you want (if either!).


That's EXACTLY what I was looking for. Both formulas will be very useful. Thank you very much, guys, you really helped me a lot!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top