Sum with a twist
You need a UDF. Call with the following
=Countalpha(B1,"S")
where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.
Function CountAlpha(target As Range, Alpha As String)
LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then
CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function
"andrew" wrote:
Hi there. I have a column consisting of the following:
S A S S S G S A A S S S S A G A S S A S
I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]
The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?
|