Sumif data from other workbook (Excel2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote