getting a sumif statement to work across multiple files

  • Thread starter Thread starter guruman
  • Start date Start date
G

guruman

Hi,

I was wondering if there was a way to get a sumif statement to wor
across different files.

Currently I have three files, one for currency conversions, one fo
items purchased for specific accounts, and third the cover shee
listing balances for all the accounts.

The cover sheet has a sumif statement linking once to the currenc
conversion file and once to the items purchased file, for differen
items. When I do this however, it gives me a '#VALUE' response. As soo
as I open the file, it links properly, but once I close it the erro
pops up. Also, if I combine the three spreadsheets into one it works
The reason I don't want to combine all items into one is because th
person using this spreadsheet only needs to see the one sheet to d
their task.

If anyone has any suggestions on how to fix this, it would be greatl
appreciated
 
guruman said:
I was wondering if there was a way to get a sumif statement to work
across different files. ....
. . . When I do this however, it gives me a '#VALUE' response. As soon
as I open the file, it links properly, but once I close it the error
pops up. . . .
Click to expand...

SUMIF accepts only range references as 1st and optionally 3rd arguments.
Ranges, as far as Excel is concerned, exist only in open files. References
to what would be ranges in other files were those files open are returned as
arrays when those files are closed.

Your only option is to replace *all* SUMIF (and COUNTIF) calls with
equivalent SUMPRODUCT calls. For example, replace

=SUMIF('C:\foo\[bar.xls]X'!A1:A10,"X",'C:\foo\[bar.xls]X'!B1:B10)

with

=SUMPRODUCT(--('C:\foo\[bar.xls]X'!A1:A10="X"),'C:\foo\[bar.xls]X'!B1:B10)
 
Hi,

I guess I dont fully understand the sumproduct formula.
Right now I have combined a fund code 'WORLD' with a currency 'CAD' in
column A, then in column B has the amount of the transaction.

If I want to sum if the item equals 'WORLDCAD' how am i to do it?

=SUMPRODUCT('C:\foo\[bar.xls]X'!A1:A10="WORLDCAD"),'C:\foo\[bar.xls]X'!B1:B10)

this is just pulling 0's for me right now.

Thanks again.
 
minor changes to what you had - more parenthesis and a *

=SUMPRODUCT(('C:\foo\[bar.xls]X'!A1:A10="WORLDCAD")*('C:\foo\[bar.xls]X'!B1:B10)
 
Back
Top