# [Solved] how to count increasing profit streak?

To calculate `Years of Increases` enter the following formula in `Cell L2`

``````=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,"")))),0)
``````

and to calculate `Years of Steady Profits` enter below formula in `Cell M2`

``````=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,"")))),0)
``````

Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference.

In case you want this formula to be dynamic i.e. after adding new column for another year you want formula to work correctly then consider the following.

In `Column K` enter some dummy character say `x` and then enter the following formula in `Cell L2`

``````=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>A2:J2=FALSE,1,""))))-1,0)
``````

and in `Cell M2`

``````=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>=A2:J2=FALSE,1,""))))-1,0)
``````

Both formulas are array formula. Drag/Copy down as required. See image for reference.

Now when you select `Column K` and insert new column, formulas will change accordingly.

Notice formula (formula bar) in the image below.

EDIT : Avoid counting streak of zeros `0-0-0-0`

For `Years of Steady Profits` use following formula in `Cell M2`

``````=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(((B2:J2>=A2:I2)*(B2:J2<>0))=0,1,"")))),0)
``````

This is an array formula.

VBA Solution :

``````Option Explicit

Sub Demo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim lastRow As Long, lastCol As Long, rIndex As Long, cIndex As Long
Dim increaseCnt As Long, steadyCnt As Long
Dim ws As Worksheet
Dim isSteady As Boolean, isZero As Boolean

Set ws = ThisWorkbook.Worksheets("Sheet2")  'change to your data sheet
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'last row with data using Column A
lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column 'last column with data using Row 2
increaseCnt = 0

For rIndex = 2 To lastRow               'loop through row 2 to last row
For cIndex = lastCol To 2 Step -1   'loop through last column to column 2
If .Cells(rIndex, cIndex) <> "NA" Then  'check for NA
If .Cells(rIndex, cIndex) <> 0 Then 'cheeck for 0
If .Cells(rIndex, cIndex) = .Cells(rIndex, cIndex - 1) Then 'compare cells for steady count
ElseIf .Cells(rIndex, cIndex) > .Cells(rIndex, cIndex - 1) Then 'compare cells for increase count
increaseCnt = increaseCnt + 1   'increment increaseCnt
ElseIf .Cells(rIndex, cIndex) <> 0 Then 'check for cell is 0
End If
Else
Exit For                'exit for loop
End If
Else
Exit For                    'exit for loop
End If
End If
Next cIndex
.Cells(rIndex, lastCol + 2) = increaseCnt   'display increaseCnt