Prev: Consolidate
Next: Using Tab key to jump to specific cell
From: Kieran on 6 Apr 2010 10:02
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
From: Luke M on 6 Apr 2010 10:27
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)

--
Best Regards,

Luke M
"Kieran" <Kieran(a)discussions.microsoft.com> wrote in message
news:5F87B8CB-6B0D-4881-ACCD-77544F6254F9(a)microsoft.com...
> 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


From: T. Valko on 6 Apr 2010 12:23
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.

--
Biff
Microsoft Excel MVP


"Kieran" <Kieran(a)discussions.microsoft.com> wrote in message
news:5F87B8CB-6B0D-4881-ACCD-77544F6254F9(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: Consolidate
Next: Using Tab key to jump to specific cell