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
steadyCnt = 0
isSteady = False
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
steadyCnt = steadyCnt + 1 'increment steadyCnt
isSteady = True 'set steady flag true
ElseIf .Cells(rIndex, cIndex) > .Cells(rIndex, cIndex - 1) Then 'compare cells for increase count
If Not isSteady Then
increaseCnt = increaseCnt + 1 'increment increaseCnt
steadyCnt = steadyCnt + 1 'increment steadyCnt
ElseIf .Cells(rIndex, cIndex) <> 0 Then 'check for cell is 0
steadyCnt = steadyCnt + 1 'increment steadyCnt
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
.Cells(rIndex, lastCol + 3) = steadyCnt 'display steadyCnt
increaseCnt = 0
steadyCnt = 0
isSteady = False
Next rIndex
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
```

See image for reference.

8

solved how to count increasing profit streak?