count recurring yes/no type data

[Guest]

I am not sure if this is possible..... I am working in a large spreadsheet
containing inventory counts. I want to be able to display if an item is
growing over period of months. Is there a way to do a count or something
that would return the number of months this would be increasing, 0=no
increase, 1=increase?
Jan Feb Mar Apr May Jun Jul Answer
0 1 1 1 1 0 0 4
1 0 0 0 1 1 0 2

 

[Bob Phillips]

=SUMPRODUCT(--(A2:F2=1),--(B2:G2=1))+1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for your reply. This won't show me the the number of consecutive
months for each row that have a value of 1 will it? I am looking to count
the highest number of recurring "1"s. Maybe Excel can't do this......

Another Example

Part# jan feb mar apr may jun Answer:
65145 0 0 0 1 1 1 3
64135 1 1 1 1 0 1 4
68112 1 0 1 1 1 0 3

 

[Bob Phillips]

It gets exactly those answer in my tests. Needs to be adjusted to the
ranges, =SUMPRODUCT(--(B2:F2=1),--(C2:G2=1))+1, but other than that ...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kounoike]

This way is to make UDF something like

Function counttest(rng As Range) As Long
Application.Volatile
Dim st0 As Boolean, st1 As Boolean
Dim s As Long, state As Long, j As Long
Dim rec() As Long
ReDim rec(rng.Count)
st0 = False
st1 = False
For Each rng In rng
If rng.Value = 1 Then
st1 = True
Else
st1 = False
End If

If st0 And st1 Then
state = 1
ElseIf st0 And (Not st1) Then
state = 2
ElseIf (Not st0) And st1 Then
state = 3
End If

Select Case state
Case 1
s = s + 1
Case 2
rec(j) = s
j = j + 1
s = 0
st0 = st1
Case 3
s = s + 1
st0 = st1
Case Else

End Select
Next
counttest = Application.Max(rec)
End Function

and apply this function in your worksheet.
assumeing your data are populated in "b2:g2" and answer in "i2", then
put the formula in "i2"
=countertest(B2:G2)
will return 3.
if you need a answer in "i3", drag and copy above formula to "i3".

keizi

 

[Guest]

You are right - I must have entered it wrong the first time - thank you!