Excel -- How Do I sum varibles lengths of cells
 

I am creating a spreadsheet for work to establish preping pars, but there is someting I need to do that I don't know how to do in excel.

I have 14 cells in one row. These 14 cells represent two weeks. Each cell contains a number that represents the projected amount of sales for that day.

I have another cell that contains the current day of week (Mon=1, Sun=7)

Now here is where the real problem for me comes. Each item on the spreadsheet has a shelflife ranging from .5 days to 7 days.

So for each item, I need to sum of the projected sales for current day and all the other days that fall in the items shelf life.

For example: I current day was 3 (Wed) and BBQ sauce had a 4 day shelf life. I would need the sum of Wed, Thu, Fri, and Sat projected sales.

For example: I current day was 3 (Wed) and Penne pasta had a 2 day shelf life. I would need the sum of Wed and Thu projected sales.

For example: I current day was 3 (Wed) and Ranch dressing had a 7 day shelf life. I would need the sum of Wed, Thu, Fri, Sat, Sun, Mon, and Tue projected sales.


I know I could do this with messy ifs and choose statements, but I have over 50 items to do this for; I need I cleaner way.

Any help is appreciated

Send me an example of your spreadsheet and I will send you a solution back. I use Excel all day long and as such I have a trick or two in this area.

I know I could make it work, I tinker with spreadsheets all day every day, but I am not sure exactly what you want here. Email is your friend.