Count letter "W" in the last 5 entries

[bmind]

Hi,
I want to count how many "W" are in the last 5 data entered. How can I do this? See attached sheet as an example - need to count the "w" in column C.

[JBeaucaire]

Does that data truly represent all the hurdles to be overcome? How about a dataset twice as long and fill in your expected results, just to be sure we understand your need. It appears a simple worksheet formula in D5 of:

=COUNTIF(C1:C5,"W")
...then copy that cell downward....does that do what you want?


If the "July" entry is in the way, perhaps you can simply move that out to the next column over and tuck the values up so they continue sequentially.

[martindwilson]

how about moving july as suggested then
=COUNTIF(OFFSET(INDIRECT(ADDRESS(MATCH("*",$C$1:$C$200,-1),3)),-4,,5),"W")

[bmind]

Does that data truly represent all the hurdles to be overcome? How about a dataset twice as long and fill in your expected results, just to be sure we understand your need. It appears a simple worksheet formula in D5 of:

=COUNTIF(C1:C5,"W")
...then copy that cell downward....does that do what you want?


If the "July" entry is in the way, perhaps you can simply move that out to the next column over and tuck the values up so they continue sequentially.

Thanks for quick reply. There are a few hurdles with this, it is not as is easy as it looks.
1. Empty space can not be deleted as data is downloaded from website and there are more then 300 sheets and have the data moved somewhere else without space is something i would like to avoid (if I can).
2. The formula (to count) will only be in a specific cell (can not change location as it will be used for another calculation.
3. When data updates from web query then more rows will be added. Formula should count "W" only in the last 5 entries (empty space does not count as an entry).

For example in the test sheet supplied, enter a formula in D5 so that when I keep adding (manually if you will) data and empty cells in column C it will calculate the number of "W" in the last 5 entries (not counting empty cells).

I hope there is more clarity now.

[JBeaucaire]

See Martin's answer in post #3. I think that does what you want.

[daddylonglegs]

Perhaps try this array formula

=SUM((ROW(C1:C100)>=LARGE(IF(C1:C100<>"",ROW(C1:C100)),5))*(C1:C100="W"))

confirmed with CTRL+SHIFT+ENTER

[bmind]

Perhaps try this array formula

=SUM((ROW(C1:C100)>=LARGE(IF(C1:C100<>"",ROW(C1:C100)),5))*(C1:C100="W"))

confirmed with CTRL+SHIFT+ENTER

This works perfect. Thanks a lot for your help!