Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Tallinn, Harjumaa, Estonia
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumif data from other workbook (Excel2000)

    Hi!

    I use Sumif function that looks for data from other workbook like this:
    SUMIF('c:/theWorkbook2.xls]sheet1'!$F$5:$F$10000;"MAT";'c:/theWorkbook2.xls]sheet1!'$G$5:$G$10000).

    I'd like to use formula witout opening theWorkbook2.xls,
    but this gives #VALUE error.

    Do I wrong something, or isn't the sumif able calculate sums in other workbook?

    Thanks for any help,
    Anu

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif data from other workbook (Excel2000)

    You can only do the sumif when the source workbook is open.

    You can create links in cells to the data in the other workbook and base your sumif on those linked cells however.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif data from other workbook (Excel2000)

    According to Micosoft KB article Q260415, SUMIF can't handle external references to closed workbooks.

    The solution is to use a combination of SUM and IF in an array formula:

    =SUM(IF('c:[theWorkbook2.xls]sheet1'!$F$5:$F$10000="MAT";'c:[theWorkbook2.xls]sheet1!'$G$5:$G$10000;0))

    This must be entered as an array formula (press CTRL+SHIFT+ENTER instead of ENTER after modifying the formula)

    (I presume that the missing open brackets [ in your formula are typos)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif data from other workbook (Excel2000)

    I'd suggest a different route, which avoids using array-formulas.

    First create dynamic name ranges in theWorkbook2.xls reagrding the ranges in F and G.

    theWorkbook2.xls:

    Change the name Sheet1 to Data.

    Activate Insert|Name|Define.
    Enter NumRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box.

    =MATCH(9.99999999999999E+307,Data!$G:$G)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter DataRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box.

    =NumRecs-(ROW(Data!$G$5)-1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Grange as name in the Names in Workbook box.
    Enter as formula in the Refers to box.

    =OFFSET(Data!$G$5,0,0,DataRecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Frange as name in the Names in Workbook box.
    Enter as formula in the Refers to box.

    =OFFSET(Data!$F$5,0,0,DataRecs,1)

    Activate OK.

    theWorkbook1.xls: (Yes, I mean workbook 1 where you have your original SUMIF formulas).

    Rename the worksheet of the original SUMIF formulas as Summary.

    In A from A2 on enter the conditions/criteria for which you want to compute the totals using data from
    theWorkbook2.xls. The first condition to be entered:

    MAT (in A2)
    DAT (in A3)
    etc.

    Back to theWorkbook2.xls:

    Insert a worksheet and rename it SumData.

    In A2 enter and copy down as far as needed:

    ='c:/[theWorkbook1.xls]Summary'!$A2

    In B2 enter and copy down as far as needed:

    =SUMIF(Frange,A2,Drange)

    Back to Summary in theWorkbook1.xls:

    In B2 enter and copy down as far as needed:

    ='c:/[theWorkbook2.xls]SumData'!$B2

    Now you can keep theWorkbook2.xls closed.

    Aladin
    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Tallinn, Harjumaa, Estonia
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif data from other workbook (Excel2000)

    Hi!

    Very many thanks to all of you. It is great help. I havent solved the problem yet. I asked user to open the other workbook too. Now I have user complaints that the sums arent sometimes calculated in right way. I checked that the formulas that give wrong results, are as should be.
    I think I should rework the workbook thus it's more reliable. I think the hints you posted here can be helpful.


    Thanks again,
    Anu

Posting Permissions

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