Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count max consecutive cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count max consecutive cells
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count max consecutive cells
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count consecutive cells then start over | Excel Worksheet Functions | |||
How can I count values in multiple non-consecutive cells? | Excel Worksheet Functions | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
Count Consecutive Cells | Excel Discussion (Misc queries) |