Results 1 to 2 of 2

Thread: Sum dynamic range in closed workbook using address/match

  1. #1

    Sum dynamic range in closed workbook using address/match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •