IF Statement or Macro? Hard to explain.See the Sample....

[Z0827]

What I am looking for is hard to explain in the title. I have two columns of data (about 35,000) rows.

One column has simply a Y or N in the cells going down. The second column is blank. I want the second column to look in the first and determine if it is a Y then the result is a 1. This is where it gets tricky. It needs to count the number of N's after a Y. So if it goes Y, N, N, N, Y the corresponding cells would read 1, 2, 3, 4, 1. I have posted some below for you to see what I mean. Hopefully this is kind of easy to understand. I am having a hard time trying to explain.

REAL Cycle
Y 1
Y 1
Y 1
N 2
Y 1
Y 1
Y 1
Y 1
Y 1
N 2
N 3
Y 1
Y 1
Y 1
Y 1
N 2
Y 1
N 2
N 3
N 4

Thanks for the help!

Zach

 

[mikerickson]

Put 1 in B1
in B2 put =(A2="N")*B1+1

and drag down

 

[njimack]

=if(a2="y",1,b1+1)

 

[Z0827]

=if(a2="y",1,b1+1)

That works perfect. I don't know why I couldn't figure that out. I have been sitting here for a while just playing with things, but forgot to try to add!

Thanks alot.