CODEWITHSUNDEEP
excel-formula
Pete Day
Pete Day

Reputation: 3

Count last 10 rows containing a word

I have a column of wins, "Yes" or "NO" in each cell. I want to Count the number of "yes" in the last 10 rows. One issue is that not every row has an answer yet but I want the blank row that has other data to be counted for the last 10. So I'm using another column that has data as the count reference. I'm not super formula savvy but have learned a lot lately. This is the formula I'm trying to use but It does not give me the desired result.

=COUNTIF(OFFSET(M1,COUNT(L:L)-10,0),"Yes")

Cell Data

Upvotes: 0

Views: 792

Answers (2)

Kelly Kuzemchak
Kelly Kuzemchak

Reputation: 197

You're really close! I think you want =COUNTIF(OFFSET(M1,COUNT(L:L)-1,0,-10),"Yes")

Row offset: If you have 25 rows of data, COUNT(L:L) will give you 25. You want to offset by 24, since we're starting from M1, so subtract 1.
Column offset: 0
Row span: We want to span backwards 10 rows, so -10

Source: https://exceljet.net/formula/average-last-5-values

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

OFFSET is Volatile use INDEX(MATCH())

MATCH(1E+99,L:L)

Will find the last row with a number in it. Then point that at column M in the index:

INDEX(M:M,MATCH(1E+99,L:L))

Which now returns the last row in Column M where there is a number in column L

to get the starting cell we subtract 9:

INDEX(M:M,MATCH(1E+99,L:L)-9)

then it just a mater of treating them like the beginning and end of a range:

INDEX(M:M,MATCH(1E+99,L:L)-9):INDEX(M:M,MATCH(1E+99,L:L))

Then wrap that in the COUNTIF:

=COUNTIF(INDEX(M:M,MATCH(1E+99,L:L)-9):INDEX(M:M,MATCH(1E+99,L:L)),"Yes")

Upvotes: 1

Related Questions