Sum dynamic range in closed workbook using address/match

Hi all,

I have a list of monthly totals in multiple closed workbooks and I want to compile that information into a new workbook based on date intervals that I define (ie start/end dates). I tried using index match to where the formula would look like =SUM('file directory\[workbook]sheet'!start date:'file directory\[workbook]sheet'!end date), but this returns an error. PS, the start date and end date correspond to the actual cells (eg A1 and A3) and is not a named range. However, I noticed that if I change the formula to =SUM('file directory\[workbook]sheet'!start date:end date), this returned the correct totals. I suppose Excel cannot have two file directory notations in a function, even though they are the same directory.

I haven't been able to figure out a formula to sum the monthly totals in the way that allows a user to define the range. The closest I got was using the index/address function to return the actual cell references, and since I know the file path, I was able to create a text string, in say cell G1, that equals ('file directory\[workbook]sheet'!start date:end date), but I cannot figure out how I can use this to =SUM(G1).

Any help is appreciated! Thanks!

Excel 2010
Cross post: excelforum.com/excel-formulas-and-functions/946128-sum-dynamic-range-in-closed-workbook-using-address-match.html

Courtesy of Tony Valko: excelforum.com/excel-formulas-and-functions/946128-sum-dynamic-range-in-closed-workbook-using-address-match.html

Try something like this...

In some other file...

A1 = Total 2
B1 = 6/1/2012
C1 = 10/1/2012

Enter this formula in D1:

=SUMPRODUCT(('C:\[Example.xlsx]Sheet1'!A2:A6=A1)*('C:\[Example.xlsx]Sheet1'!B1:Y1>=B1)*('C:\[Example.xlsx]Sheet1'!B1:Y1<=C1)*'C:\[Example.xlsx]Sheet1'!B2:Y6)

Use the appropriate path.