SUM and LOOKUP Formula that changes with references

Good afternoon. I'm stumped on trying to setup a formula that moves with a changing reference and multiple criteria. For example, I have a file that measures labor productivity by pay periods, which I have numbered, and locations. I'm using a drop down for Period numbers and want to use that as a static reference for a lookup. For instance, if I choose Pay Period 10, I want it to include Pay Period 10 and the preceding 9 periods to sum up. When Pay Period 11 hits, I want to be able to change the drop down to Pay Period 11 and have the formula automatically sum that pay period and the preceding 9. I was trying different iterations of SUMIFS, MATCH and OFFSET. Nothing seems to work.

I hope that makes sense.

You can do it with SUM(INDEX(...):INDEX(...)), but it is difficult to be more specific as you have given no details about how your data is laid out (cells/columns used etc.)

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

Thank you Pete. I have attached a sample file.

You can put this formula in cell BJ6:

=SUM(INDEX($C6:$BH6,MATCH($B$4,$C$5:$BH$5,0)-9):INDEX($C6:$BH6,MATCH($B$4,$C$5:$BH$5,0)))

then copy down. For the 20 pay periods you just need to change the -9 to -19.

Hope this helps.

Pete

Fantastic!!!!!!!!!!!! Thank you Pete!!!!!!!!!!!!!!!

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Done and done. That worked Pete. Thanks for your help!