How to count consecutive Yes cells with the ability to reset count when a No is entered

[sara1357]

Good evening all,

I am looking for a formula that will allow me to count up how many times a 'Y' is entered consecutively on my spreadsheet.
The spreadsheet will have dates across the top and names down the side.

I would like the formula to recognise that when a N has been entered it needs to reset and start counting up the Y's again from 0.

Please see the example spreadsheet attached - I would like the formula to calculate the last column for me (I have manually entered the numbers to show you what the final number would be if the calculation was correct).

Any help appreciated.

Thanks
Sara

[Rick Rothstein]

Maybe this formula does what you want...

=IFERROR(9-LOOKUP(2,1/(B3:I3="N"),COLUMN(B:I)),COLUMNS(B:I))

Note: If your range is different from B:I, change all of the B's to the first column letter and all of the I's to the last column letter.

[JBeaucaire]

This worked for me, in J3, then copied down:

=8-IFERROR(LOOKUP(2,1/(B3:I3="N"),COLUMN(A3:H3)),0)

The part in red is the same number of columns as the part in blue, just offset back to the first column. It's just a way of counting from 1 to 8 easily in a formula.

[sara1357]

Thanks for this Rick, unfortunately this doesn't seem to be working for me, but really appreciate the response

[sara1357]

Hi Jerry

Thank you so much this is working great
I would now like to use this formula on a much larger worksheet, is it easy to adapt this formula? For example... do you use the number 8 as there are 8 data columns? so if I had for example 50 columns would I use 50-IFERROR at the start?

Thanks
Sara

[JBeaucaire]

Hypothetically, yes. Your sample data showed all 50 columns already had Y/N answers. As long as that is true for the new 50 column-sized data, this simple formula would work.

[MarvinP]

Hi Sara and welcome to the forum,

Here is another solution that I like better. It takes a little VBA to build a StrReverse function to reverse that string and then use Concat(). See if you like this one better.

StrReverse Consecutive Y.xlsm

[sara1357]

Thanks for this Marvin, that works great