Count max consecutive cells

  • Thread starter Thread starter Kieran
  • Start date Start date
K

Kieran

Col A Col B ColC Col D Col E Col F Col G
Col H
Row1 . . . x . .
3
Row2 x x . x . .
2
Row3 . . . . .
5
Row4 . . H x x
3

Ineed to find a formula that will count the maximum number of times "."
and/or "H" appears in a row. In the above example Column H gives me the
results I'm looking for. Sometimes the cells included in the range will be
blank. I need the formula to ignore blank cells.

thanks
 
This might be easier to just use an UDF:



Righ click on sheet tab, view code. Goto Insert - Module, paste the
following in.
'============
Function MaxCount(r As Range) As Integer
'Define your criteria
xCrit1 = "x"
xCrit2 = "H"

'Setup starting values
MaxCount = 0
xCount = 0

For Each c In r
If c = xCrit1 Or c = xCrit2 Then
'If matches criteria, add to count
xCount = xCount + 1
Else
'If consecutive streak is broken, compare to max
If xCount > MaxCount Then
MaxCount = xCount
xCount = 0
End If
End If
Next
If xCount > MaxCount Then
MaxCount = xCount
xCount = 0
End If
End Function
'===============

Close the Visual Basic Editor.
In your workbook, input the formula:
=MaxCount(A1:H1)
 
Try this array formula**.

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(A1:H1,{".","H"},0)),COLUMN(A1:H1)),IF(ISNA(MATCH(A1:H1,{".","H"},0)),IF(A1:H1<>"",COLUMN(A1:H1)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Back
Top