How do I use a variable as a cell reference?

[kpm30519]

I have 8000+ rows, with a few being added each day. I need to run calculations on the last ten, 20, or other number rows throughout the day, but with rows being added every day, I have no fixed point from which to start. The last entry could be in row D2520 this morning, but D2564 this afternoon.

I can get the value from the last filled cell (LOOKUP(2,1/(D:D<>""),D:D)) and the row number of the last filled cell (LOOKUP(2,1/(D:H<>""),ROW(D:H))), but I don't know how to, for example, SUM(DLastRowNumber:DLastRowNumber-10). I was tinking a variable equal to the number of rows I need but I don't know how to implement it in a cell reference.

If there is another solution, I'm all for it. I'd rather a formula, but a macro or function is fine. FYI, I'm a VBA newbie.

Thanks in advance.

 

[Scott T]

This video may help you
https://youtu.be/v8GeZlWxu6M

 

[Eric W]

I haven't seen the video, but here's one option, if the last row number (your second formula) is in F6:

=SUM(INDEX(D:D,F6-9):INDEX(D:D,F6))

 

[kpm30519]

So, the above works, but I have an array, so I tried COUNTIF((INDEX(D:H,(U59-9)):INDEX(D:H,U59)),S51), but I get a #REF error.

Ideas?

 

[Eric W]

Are you trying to do a COUNTIF from D91:H100 (if your last row is 100)? Then try:

=COUNTIF((INDEX(D:D,(U59-9)):INDEX(H:H,U59)),S51)

First cell (identified with the INDEX) is the upper left, next cell is bottom right.

 

[kpm30519]

OMFG...

I've been banging my head for hours on this and just didn't see it.

If anyone is interested, my final formula is this: =COUNTIF((INDEX($D:$D,(LOOKUP(2,1/($D:$H<>""),ROW($D:$H)))):INDEX($H:$H,(LOOKUP(2,1/($D:$H<>""),ROW($D:$H)-$R53)))),S$51)

This will take the last X non- empty rows of data (X is stored for now in R53) dynamically from array (D:H), and count the number of "hits" (S51). It is slow, but it work. If you see something wrong, or if you can suggest a better way, I'd really appreciate any thoughts.