Rolling change formula

Ok below is a sample of the report I am using at work and what I am trying to do is institue a formula based on the due date I need to add up everything in collum F from the oldest date to TODAYS date but I need it to be a rolling change so that if I open the report my past due will match every thing from today back without having to change the cell number of collum E in the formulla every day.

If anyone could help me with this I would really appreciate it.


a b c d e f
acct # order # order date/status model / sn due date qty
328538 2/15/13 3/17/13
JH3 328538 ZFLNWM GMH5992X SN 0126092 1
JH3 328538 ZFLNWM DMH5992R SN 1015104 1
328539 2/15/13 3/17/13
JH3 328539 ZFLNWM DMH5992X SN 1016093 1
JH3 328539 ZFLNWM GMH5992X SN 0129002 1
328731 2/26/13 3/28/13
JH3 328731 ZFLNWM GMH5992R SN 0131138 1
JH3 328731 ZFLNWM DMH5992X SN 0710150 1
328732 2/26/13 3/28/13
JH3 328732 ZFLNWM GMH5992X SN 0602022 1
JH3 328732 ZFLNWM GMH5992X SN 0602076 1
JH3 328732 ZFLNWM GMH5992X SN 0816053 1


Thank you

Hi and welcome to the forum

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Try this formula

=SUM($F$1:INDEX(F:F,MATCH(TODAY(),E:E)))

PAST DUE forum Harribone.xls

Because the date only shows up once for several quantities on each record I have put a formula in Col G to total up for each date (e.g. Col A has 83900 appear several times but the date is at the end and the total needs to be shown on the same row which in this case is 6).

Then a sumif formula can be used to check anything that is due (including today) and this is totalled up. I only get 230 not 232 though if you can double check which is right just in case i've done something wrong.

EDIT: Just to point out G1 has just got the value 1 as starting point for the formulas below.