Dates and Values

[improveddc]

Hi,

Is there a formula which would allow for a cell to look at a list of data which is sorted by date and add the values in another column between a specified period.

To explain - i have a list of figures which automatically update over time. Column S lists the date and Column X has the values i want to add together.

I would like to be able to add the values from column x for a period of the last 7 days for example so from todays date less 7. Which would be simple if the date entries were consistent but they are not, some sheets don't have values in the last 7 days for example.

The current formula i have is
=SUM((INDIRECT($A5&"!$X$2"))INDIRECT($A5&"!$X$4")))

which i have manually adjusted to the correct cell references. It is this i would like to automate so no matter what dates are listed the formula refers to the required period.

Any help much appreciated.

[daddylonglegs]

Try using SUMPRODUCT like this

=SUMPRODUCT(--(INDIRECT($A5&"!$S$2:$S$100")>TODAY()-7),INDIRECT($A5&"!$X$2:$X$100"))

That will sum everything in column X where column S date is more recent than 7 days ago. You'll need another condition if you want an upper limit on the date....

[improveddc]

Excellent,

Spot on thanks, all the variants i will be doing will be ranges from today so this will work great.

Thanks

[improveddc]

Is there a similar formula i can use to find the MAX in a given period?