Formula for counting cells consecutively

**d1scopants** · 09-25-2012, 04:07 PM

First of all, hello - I have found these forums to be invaluable over the years and have always found any answers in previous posts, but not this time......

So if anyone could help me with the following I would be ever so grateful.

My problem is that I want my destination cell to show the maximum number of times 'X' appears consecutively in a single row so that the answers in the attached worksheet would show:

M2 = 3
M3 = 8
M4 = 2
M5 = 12

Xinarow.xlsx

**protonLeah** · 09-25-2012, 04:24 PM

Use the COUNTIF function

**Special-K** · 09-25-2012, 04:25 PM

This seems to work, but I don't have a clue how

Array Formula
=MAX(FREQUENCY(IF(ISNUMBER(MATCH(A2:L2,{"","x"},0 )),COLUMN(A2:L2)),IF(ISNA(MATCH(A2:L2,{"","x"},0) ),IF(A2:L2<>"",COLUMN(A2:L2)))))

Source: http://www.excelbanter.com/showthread.php?t=260780
See T Valko's post

**daddylonglegs** · 09-25-2012, 04:29 PM

I don't think that works for the sample shown, though, Special-K, e.g. that give 8 for row 2 not 3 as required

You can use a simpler version for this, i.e.

=MAX(FREQUENCY(IF(A2:L2="x",COLUMN(A2:L2)),IF(A2:L2<>"x",COLUMN(A2:L2))))

confirmed with CTRL+SHIFT+ENTER

**d1scopants** · 09-25-2012, 04:44 PM

Firstly, my thanks Special-K for you quick endeavours, but daddylonglegs has got what I needed - this is one headache I needn't worry about now.

THANKYOU!!

**d1scopants** · 09-26-2012, 08:59 AM

Actually, perhaps my headache continues..... it seems that I need to find out the maximum times it appears in a row over consecutive rows, i.e. where the next row of data is seen as a continuation.

I have attached a worksheet which should make it a little clearer - I have put in the array formula (from daddylonglegs) for the first line of data as this still works, but can't fathom how to alter it to suit, so that the answer for B2 to A3 should be 5.

Thanks again for any help/patience!

wsheet1.xlsx

**d1scopants** · 09-28-2012, 08:25 AM

Still haven't managed to work it out yet.....

Save me from this despair!

**JosephP** · 09-28-2012, 09:11 AM

hi-please see forum rule 8

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.

please add links here to your cross-posts in any other forums. thanks :-)