# Need formula or VBA count the delays

#### motilulla

##### Well-known Member
Hello,</SPAN></SPAN>

Note: Colours in column D are filled just to explain the example purpose </SPAN></SPAN>

In the column D I got some sequence of numbers and I got a formula, which counts the length of 0's in the G6:G19 now I need a formula, which can count delay of the 0's length in H6:H19</SPAN></SPAN>

For example delay of the 0 with length 1, last 0 find in row 59 and the last data row is 65 so from the row "59 to 65" 0 is getting delay 6 </SPAN></SPAN>

For example delay of the 0 with length 2, last 00 find in row 21&22 and the last data row is 65 so from the row "22 to 65" 00 is getting delay 43 and so on for all delays...</SPAN></SPAN>

Here is an example... </SPAN></SPAN>

Book1
ABCDEFGH
1
2
3
4
5Of 1'sLength Of 0Count Of 0Delay
60186
712243
82321
9340
10450
1106132
12170
13280
14090
15010110
160110
171120
180130
190140
201
210
220
231
240
251
260
271
280
290
300
310
320
330
341
350
361
370
381
392
403
410
421
432
443
454
460
470
480
490
500
510
520
530
540
550
561
572
583
590
601
612
620
630
640
651
Sheet4
Cell Formulas
RangeFormula
G6{=SUM(0+(FREQUENCY(IF(\$D\$6:\$D\$65=0,ROW(\$D\$6:\$D\$65)),IF(\$D\$6:\$D\$65<>0,ROW(\$D\$6:\$D\$65)))=F6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

Last edited:

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Bump

Try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1085206a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1085206-need-formula-vba-count-delays.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc

n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row

va = Range([COLOR=brown]"D1:D"[/COLOR] & n + [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]

[COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
x = [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Do[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
x = x + [COLOR=crimson]1[/COLOR]: i = i + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Loop[/COLOR]
vb(i, [COLOR=crimson]1[/COLOR]) = x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR], [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR]
[COLOR=Royalblue]For[/COLOR] j = n [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]6[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]If[/COLOR] vb(j, [COLOR=crimson]1[/COLOR]) = i [COLOR=Royalblue]Then[/COLOR]
vc(i, [COLOR=crimson]1[/COLOR]) = n - j: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"H6"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vc
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]``````

Please try at H6 Press Ctrl+Shift+Enter and drag down

=IFERROR(COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6))),"")

Please try at H6 Press Ctrl+Shift+Enter and drag down

=IFERROR(COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6))),"")
Hello Bo_Ry, I think function "=IFERROR" does not work due to I am using excel 2000</SPAN></SPAN>

</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>

You may ignore the error shown, press Ctrl+Shift+Enter

=COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6)))

or
=IF(ISERROR(COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6)))),"",COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6))))

Try this:

Code:
``````[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1085206a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1085206-need-formula-vba-count-delays.html[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], n [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va, vb, vc

n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=royalblue]End[/COLOR](xlUp).Row

va = Range([COLOR=brown]"D1:D"[/COLOR] & n + [COLOR=crimson]1[/COLOR])
[COLOR=royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]

[COLOR=royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
x = [COLOR=crimson]1[/COLOR]
[COLOR=royalblue]Do[/COLOR] [COLOR=royalblue]While[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
x = x + [COLOR=crimson]1[/COLOR]: i = i + [COLOR=crimson]1[/COLOR]
[COLOR=royalblue]Loop[/COLOR]
vb(i, [COLOR=crimson]1[/COLOR]) = x
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]

[COLOR=royalblue]Next[/COLOR]

[COLOR=royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR], [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR]
[COLOR=royalblue]For[/COLOR] j = n [COLOR=royalblue]To[/COLOR] [COLOR=crimson]6[/COLOR] [COLOR=royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
[COLOR=royalblue]If[/COLOR] vb(j, [COLOR=crimson]1[/COLOR]) = i [COLOR=royalblue]Then[/COLOR]
vc(i, [COLOR=crimson]1[/COLOR]) = n - j: [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]For[/COLOR]
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
[COLOR=royalblue]Next[/COLOR]
[COLOR=royalblue]Next[/COLOR]

Range([COLOR=brown]"H6"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vc
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]``````
Excellant! Akuini, with your code I got the results perfect! </SPAN></SPAN>

</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>

You may ignore the error shown, press Ctrl+Shift+Enter

=COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6)))

or
=IF(ISERROR(COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6)))),"",COUNT(\$D\$6:\$D\$99)+ROW(\$D\$6)-SMALL(IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)),MATCH(2,1/(FREQUENCY(IF(\$D\$6:\$D\$99=0,ROW(\$D\$6:\$D\$99)),IF(\$D\$6:\$D\$99<>0,ROW(\$D\$6:\$D\$99)))=F6))))
Thank you Bo_Ry, both the formula worked perfect!</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>

Excellant! Akuini, with your code I got the results perfect! </SPAN></SPAN>

</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>
Akuini, find that if the data in column D finishes with 0 at the end instead 1 as shown in the post#1 VBA stop at the line below</SPAN></SPAN>
Code:
`` Do While va(i + 1, 1) = 0``
</SPAN></SPAN>
</SPAN></SPAN>

Thank you

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>

Akuini, find that if the data in column D finishes with 0 at the end instead 1 as shown in the post#1 VBA stop at the line below
Rich (BB code):
`` Do While va(i + 1, 1) = 0``

Thank you

Kind Regards

Moti

Code:
``````        Do While va(i + 1, 1) = 0
x = x + 1: i = i + 1
[COLOR=#0000ff]If i = n Then Exit Do[/COLOR]
Loop``````

Replies
11
Views
182
Replies
8
Views
378
Replies
7
Views
175
Replies
12
Views
276
Replies
0
Views
249

1,207,402
Messages
6,078,270
Members
446,324
Latest member
JKamlet

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

### Which adblocker are you using?

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

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