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
"Peggy Sue" <(E-Mail Removed)> wrote in message
news:25D3F989-0290-4D47-8961-(E-Mail Removed)...
> 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" wrote:
>
> > =SUMPRODUCT(--(A2:F2=1),--(B2:G2=1))+1
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Peggy Sue" <(E-Mail Removed)> wrote in message
> > news:F83E45B6-F24D-4C1C-9699-(E-Mail Removed)...
> > > 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
> >
> >
> >
|
Similar Threads
