Sum Specific Range in Dynamic Excel File

**mwgr5** · 07-29-2008, 12:39 PM

I have a data file that has news rows added to it every day. I want to be able to sum the seven rows of data automatically, so only the last seven days of data are used in my calculations. Is this possible?

Also, I have another data sheet that has multiple enteries per day. Is it possible to only sum the last 7 days of data. There are more than seven rows of data but only for seven days. Thanks for the help.

**Ron Coderre** · 07-29-2008, 01:29 PM

If we knew the structure of your data, that would help us understand your issue. Can you describe the data...or, even better...post a zipped Excel file with sample data? (Hopefully, there's a date column that we could use for criteria.)

**mwgr5** · 07-29-2008, 02:02 PM

An outline of the data structure:

Column A:|Column B:
Date1| Number 1
Date2| Number 2

There are new days with corresponding numbers added each day. Each entry adds a new row. I want sum the numbers corresponding to the last seven days.

**ChemistB** · 07-29-2008, 02:57 PM

Last Seven days or last 7 data points (or are they always synonymous)?

ChemistB

**mwgr5** · 07-29-2008, 03:26 PM

They are always synonymous. Except one data sheet has more rows than days. So there could be 10 rows of data for 7 days, but I want to sum the last seven days, not exactly the last seven rows.

**Ron Coderre** · 07-29-2008, 04:43 PM

Let's see if I understand what you want.....

With
A2:A50 will hold dates, initially only A2 and other dates will be added below.
B2:B50 will hold amounts corresponding to the entered dates

This formula sums the Col_B values for
the last date entered in Col_A
AND
the previous six days:

C1: =SUMIF(A2:A50,">="&(LOOKUP(10^99,A2:A50)-6),B2:B50)

so....
If A35 contains 28-JUL-2008 AND is the last date at the bottom of the list,
that formula will sum the amounts from 22-JUL-2008 through 28-JUL-2008
(a 7 day range)

Note: That 7-day range could be more than 7 rows if any dates have more than one entry.

EDITED TO INCLUDE THE FOLLOWING:
If the Col_A dates will ALWAYS be in ascending order...
this formula returns the sum of values for the
dates Max_Date through Max_Date_Minus_6