Offset and Countif.

[wolverineandrew]

This is going to be an extremely easy solution im sure. Ive never used offset before and im not sure how to work it in to existing formulas. I want to automatically countif the last 10 entries (i constantly add to the bottom of the column) say the word "win" from column e2:e200. Please help.

[Pepe Le Mokko]

I want to automatically count the last 10 entries (i constantly add to the bottom of the column) that say the word "win" from column e2:e200. Please help.

Looks to me like the answer is 10 ...

[wolverineandrew]

..................

[Fotis1991]

Do you have a sample workbook for this?

[wolverineandrew]

I dont, i can make one though. But its exactly as i said. Just 1 column. And i need to know how to countif the last 10 cells in it say the word win. And im constantly adding to the bottom of the list.
A:
loss
loss
win
win
loss
loss
loss
win
win
loss

[Fotis1991]

although I no mood to joke, the answer (as pepe told) is 10.... Obviously you mean something else that we can not understand...

[wolverineandrew]

Ok, here is what im talking about. This.xlsx And how is that answer 10? There is clearly only 4 wins in the last 10 cells in my previous post. Every day i add to the bottom of that column and i want to know how many of the last 10 say win. But have it automatically update as in enter more information.

[Pepe Le Mokko]

Perhaps something like

Please Login or Register  to view this content.

(asuming you want to count the number o tmes "win" appears in the last ten rows)
Adapt the ranges to your needs
Can also be done with OFFSET but wanted to avoid volatile functions

EDIT replace A1:A20 with A:A in the formula to accommodate for varying lengths

[Smadexcel]

Hi

might need to change the -10 to -9 ?

Mike

[Pepe Le Mokko]

Hi

might need to change the -10 to -9 ?

Mike

Thx Mike absolutely right

[wolverineandrew]

Finally a real answer thank you. now would one of you last few guys mind explaining the set up for me? Because when i enter it into mine and replace them with my e2:e200 area it doesnt come up with the correct answer. Heres my thing updated. And it comes up with 7 as the answer but clearly the answer should be 6 not 7 like it says. Help : )This.xlsx

[Smadexcel]

Hi
Try removing the blank in E2 or changing e2:e200 to e3:e200

[wolverineandrew]

Yes, thank you so much! finally. And out of curiosity. If im looking for the last 10, how come its a -9 in there?

[Pepe Le Mokko]

Perhaps it's the fact that my mother tongue isn't English, but I find your ironic remarks (Finally a real answer thank you. now would one of you last few guys mind explaining the set up for me - Yes, thank you so much! finally.(sic)) quite disturbing. All people out here are volunteers, helping you for FREE on their spare time. I think you should respect that and keep your remarks to yourself. ( or add a smiley )
As for the -9, give it some thought, you'll find the answer easily

[wolverineandrew]

My comments are my comments. I asked for help and you made a joke out of it. And most recently i again asked for help because i didnt know the answer and you just told me to think about it. If i knew the answer or could have figured it out i wouldnt have posted. I do appreciate every one on here and the time they donate to help others in need. But when you come onto a thread and dont really attempt to help and just make jokes then those comments are going to come. p.s. Your reading too far into a simple comment expressing my joy that i got some one to help me.

[Fotis1991]

So, Pepe, made a joke.

Not ridiculed and not manifested no inclination to underestimate the effort that some people did to help you.
In 2 Posts that i sent, made ​​it clear that although I had no disposition joke, did not understand the question.
Once the cleanup, Pepe gave you your solution.

Your answer.

...Finally a real answer..

Is ironic for people that are volunteers, helping you for FREE on their spare time. I think you should respect that and certenly you didn't.

Edit: Also English is not my native language.