Count backward the # if times a text shows up since another text?

[650nmWolf]

I am trying to count the number of times a certain text shows up in a row since the last time another text shows up in that row. Example: I would like to know the number of times the word "apple" shows up since the word "orange" showed up. C2 is "orange," H2 is "apple," L2 is "orange," then N2 is "apple," and Q2 is "apple." Is there a formula to count "apples" since the last "orange?" The answer going backwards (toward column A) would be 2. Is that even possible?

 

[Marcelo Branco]

Assuming data in A2:Z2, maybe...

=COUNTIF(INDEX(A2:Z2,LOOKUP(2,1/(A2:Z2="orange"),COLUMN(A2:Z2)-COLUMN(A2)+1)):Z2,"apple")

M.

 

[650nmWolf]

Marcelo,
Thanks so much for helping me with this. I am trying to work that formula into my spreadsheet. It is taking me a little time since I am unfamiliar with some of the functions and I think I oversimplified my question to the forum. I will crank this into my numbers and hopefully come back with a more intelligent question. Great stuff so far! thank you!