Count if formula

[Brew]

How do I create a formula that counts the N and the following Y in the list and if not then x.

example
T4:T13 = N,N,N,N,N,N,N,Y,Y,Y
formula Result
U4:U13 = 1,2,3,4,5,6,7,8,9,x,x

 

[Domenic]

Is this what you mean?

U4, copied down:

=IF(OR(T4={"N","Y"}),SUM(COUNTIF(T$4:T4,{"N","Y"})),"X")

 

[Dufus]

Does this work?
=IF(AND(T3="N",T4="Y"),COUNTA($T$4:T4),IF(T4="N",COUNTA($T$4:T4),"X"))

Dufus

 

[Brew]

Domenic, your formula, just does a continous numerical count of all records.
Dufus, your formula counts correctly, however, after x, when another N record appears, then recount. see expanded example.

example
T4:T15 = N,N,N,N,N,N,N,Y,Y,Y,N,N
formula Result
U4:U15 = 1,2,3,4,5,6,7,8,9,x,x,1,2
thanks

 

[Brew]

I need the formula to do execute like the following example:
example
T4:T18 = N,N,N,N,N,N,N,Y,Y,Y,N,N,Y,Y,N
formula Result
U4:U18 = 1,2,3,4,5,6,7,8,9,x,x,1,2,3,x,1

 

[Brew]

Does anyone know if code would be more efficient for this problem

 

[bosco_yip]

Does this help ?

U4 =IF(T4="Y","x",IF((T4="N")*(T3="Y"),1,U3+1))

Regards
Bosco

 

[Brew]

thanks bosco_yip, i gonna go with this approach.