Running Counter with criteria

[kk1352]

Hi all,

I'm trying to work out the following problem: I need a formula which will number all instances of a particular value (e.g the value “T”) which fall between certain values (“I” and “RT”) in a corresponding column. This number will increase incrementally each time the triggering cell ("I") is encountered. I have attached an example spreadsheet.

Any help would be greatly appreciated!

[hrlngrv]

Looks like you're starting at the bottom and going up. Are the values in B6:C23 the intended results? In which case the R in A23 triggers the 1 in C23, but there's also a 1 in C22, so it appears you want to record state changes, that is, the I in A24 triggers a state change for the R values in A22:A23 and the T values in A20:A21. The RT value in A18 and the I value in A17 then trigger a state change, and so forth. Would every cell in col A containing RT have the cell above it in col A contain I?

[kvsrinivasamurthy]

In B5 then copied across.

=IF($A5=B$3,LARGE(COUNTIF($A5:$A25,{"I","RT"}),1),"")

[kk1352]

Thats brilliant thank you so much!

[kvsrinivasamurthy]

welcome.Thanks for feed back.

[kk1352]

Hi kvsrinivasamurthy,

Just had a question regarding the solution you came up with. If i wanted to add another variable to the formula e.g. anything also starting with the text "RT, I" will also trigger the counter to add +1? I have tried changing to =IF($A5=B$3,LARGE(COUNTIF($A5:$A25,{"I","RT","RT, I*"}),1),""), or =IF($A5=B$3,LARGE(COUNTIF($A5:$A25,{"I","RT*"}),1),"") but neither of these seem to work.

[JeteMc]

Applying the second formula to the file attached to post #3 seems to yield the expected results when I changed A10 and A24 to RT,I
Perhaps it would help if you could explain what seems to not work.
Let us know if you have any questions.

[kk1352]

All good, I managed to work it out. If i changed the formula to =IF($A5=B$3,LARGE(COUNTIF($A5:$A25,{"*I *","RT"}),1),"") it worked the way i wanted it. Thanks!

[JeteMc]

Glad that you were able to resolve the issue. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.