Counting the number of occurances with consecutive values above zero

**bryan3228** · 03-06-2015, 03:37 PM

I am trying to count the number of instances where an item has received orders in consecutive months.

In column P I have hardcoded the answers but I want to find a formula that will do this for me

Book3.xlsx

**samba_ravi** · 03-06-2015, 03:48 PM

=SUM((FREQUENCY(IF(B2:M2>0,COLUMN(B2:M2)),IF(B2:M2<=0,COLUMN(B2:M2)))>0)*1)
try this array formula(Shift+Ctrl+Enter) and copy towards down

**bryan3228** · 03-13-2015, 09:35 AM

Unfortunately that didn't quite work. If an item was ordered for one month, it is counting that in the total. One month shouldn't be calculated. An item would need to be ordered for two months or more

**JieJenn** · 03-06-2015, 03:57 PM

If your report gets too big, UDF might be a better option since it doesn't require as much memory as array formula.

Public Function ConsecutiveOrders(ByVal PeriodRange As Range) As Integer
Dim iCount As Integer
Dim vPrvValue As Variant
Dim cell As Range

iCount = 0
vPrvValue = 0

For Each cell In PeriodRange
    If cell.Value <> 0 And vPrvValue = 0 Then
        iCount = iCount + 1
    End If
    vPrvValue = cell.Value
Next cell

ConsecutiveOrders = iCount

End Function