Count number of rows since a specific word most recently appeared in a cell

[Ironman]

Hi

Column A contains consecutive dates (most recent date in bottom row, added to daily) and Column B contains text relating to the date in Column A.

What I am looking for is a formula for the number of days (rows) since the most recent occurrence of the word REST in Column B.

Many thanks!

 

[yky]

Is REST the entire content of a cell or it's part of a string?

 

[Rick Rothstein]

Is REST the entire content of a cell or it's part of a string?

In addition to yky's question, I would also like to know what row your data starts on (Row 1 with no headers, Row 2 with headers)?

 

[yky]

Try this:

=LOOKUP(2,1/A1:A100,ROW(INDEX(A:A,1):INDEX(A:A,100)))-LOOKUP(2,1/SEARCH("rest",B1:B100),ROW(INDEX(A:A,1):INDEX(A:A,100)))

You may need to adjust the boundary. I just arbitrarily chose rows 1 to 100.
If the last row that contains REST is included, you want to add 1 to the result.

 

[Ironman]

Hi all

yky - REST is the entire content of the cell

Rick - Data starts on Row 2 (with headers). I'm actually on Row 6722 now and it increases by 1 every day.

yky - Your formula works perfectly - THANK YOU!!!!!

 

[Peter_SSs]

You could also try

=MAX(A1:A8000)-AGGREGATE(14,6,A1:A8000/(B2:B8000="REST"),1)-1

 

[Ironman]

Many thanks Peter, very neat working solution.

 

[Peter_SSs]

Many thanks Peter, very neat working solution.

You're most welcome.