how to count streak in range ascending

nadavrock

New Member
Joined
May 15, 2019
Messages
24
i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Assumes your data is in column A. Here is a VBA solution

Code:
Option Explicit


Sub CountColA()
    Dim x As Long
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 1 To lr
        If Range("A" & i) > 0 Then
            x = x + 1
        End If
    Next i
    MsgBox ("Total Number above zero is " & x)
End Sub
 
Upvote 0
i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?

with the below what answer would you want returned?

Excel 2010
A
17
22
30
47
50
62
77
810
96
1010
110
120
131
146
157
160
Sheet4


and what answer would you want returned if A16 was 4 rather than zero?
 
Upvote 0
Assuming your numbers are in Column A starting at Row 1, this UDF (user defined function) will return the length of the longest contiguous run of numbers greater than zero...
Code:
[table="width: 500"]
[tr]
	[td]Function Streak()
  Dim x As Long, Arr As Variant
  Arr = Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))
  For x = 0 To UBound(Arr)
    If Len(Arr(x)) > Streak Then Streak = Len(Arr(x))
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=Streak()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Rick, hopefully you are right that the OP wants the longest streak as I am not sure exactly what they mean by

i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0
Which is why I made post number 3.
:biggrin:
 
Last edited:
Upvote 0
i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?

Is this what you are looking for?
[TABLE="width: 136"]
<colgroup><col width="68" span="2" style="width:51pt"> </colgroup><tbody>[TR]
[TD="width: 68"]Data[/TD]
[TD="width: 68"]Streak[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Try this formula
=IFERROR(IF(A2<>0,B1+1,0),1)
NOTE: dont post this formula into row 1, start it at row 2 or else you will have manually populate the first cell with a number 1 and then fill in the formula below it
 
Upvote 0
thanks for the replies. my intention was not the longest streak but the latest streak.
with data as listed below. the latest streak read from the top would be 3 in a row greater than 0. that is what i want to display in a seperate cell. how do i do this?

a1 3
a2 4
a3 8

a4 0
a5 1
a6 1
a7 1
a8 1
 
Upvote 0
thanks for the replies. my intention was not the longest streak but the latest streak.
with data as listed below. the latest streak read from the top would be 3 in a row greater than 0. that is what i want to display in a seperate cell. how do i do this?

a1 3
a2 4
a3 8

a4 0
a5 1
a6 1
a7 1
a8 1
Give this UDF a try (install instructions in Message #4, note function name change though)...
Code:
[table="width: 500"]
[tr]
	[td]Function LatestStreak()
  LatestStreak = Len(Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))(0))
End Function[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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