Results 1 to 5 of 5

20020419, 10:52 #1
 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

20020419, 11:04 #2
 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.jkpads.com
Professional Office Developers Association

20020419, 11:12 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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)

20020421, 20:02 #4
 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 arrayformulas.
First create dynamic name ranges in theWorkbook2.xls reagrding the ranges in F and G.
theWorkbook2.xls:
Change the name Sheet1 to Data.
Activate InsertNameDefine.
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.
AladinMicrosoft MVP  Excel

20020426, 09:55 #5
 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