count of consecutive cells with same value omitting the first three

[Giletant]

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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	1	1	1	1	1	0	0	1	0	0	0	0	0	1	1	0	0	1	0	0	0	0	0	1	0	0	0	1	1	1	1	27

<tbody>
</tbody>

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.

 

[mopp1]

=sumproduct(--(a1:ae1<>""))-sumproduct(--(a1:ab1=0)*(b1:ac1=0)*(c1:ad1=0)*(d1:ae1=0)*(a1:ab1<>"")*(b1:ac1<>"")*(c1:ad1<>"")*(d1:ae1<>""))

 

[MickG]

Try this for row 1.

[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

 

[Giletant]

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

 

[Tetra201]

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

 

[Giletant]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	1	1	1	0	0			0	0	0	1	0			0	0	0	1	0			0	0	0	1	1			0	1	1

<tbody>
</tbody>

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

 

[Tetra201]

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

 

[Eric W]

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

 

[Giletant]

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!

 

[Eric W]

Glad we could help, and thanks for the feedback!