need sum from another workbook

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

Checkbook register in Workbook A, payees in Col K, debits in Col M.
Need formula in Workbook B to show total of all amounts paid to "Food City"
Ideally, Workbook A would not have to be open.

Any way to do this?
 
This worked ok for me:
=SUMIF([book1.xls]Sheet1!$A$1:$A$25,"food city",[book1.xls]Sheet1!$B$1:$B$25)

Adjust names/addresses as required.
 
David

This works for a fixed range

=SUMIF([Book1]Sheet1!$K$6:$K$8,"Food City",[Book1]Sheet1!
$M$6:$M$8)

Regards
Peter
 
Dave Peterson wrote
This worked ok for me:
=SUMIF([book1.xls]Sheet1!$A$1:$A$25,"food
city",[book1.xls]Sheet1!$B$1:$B$25)

Adjust names/addresses as required.
Click to expand...

Excellent. Less cumbersome than what I came up with:
=SUMPRODUCT(([FOODCOST.XLS]Nov!$M$5:$M$34)*([FOODCOST.XLS]Nov!$K$5:$K$34
="Food City"))
 
Dave Peterson wrote
This worked ok for me:
=SUMIF([book1.xls]Sheet1!$A$1:$A$25,"food
city",[book1.xls]Sheet1!$B$1:$B$25)
Click to expand...

Oops! Results in #VALUE if "book1.xls" not open. Mine worked if source book
was closed.
 
Yep. Bad testing.

I hit F2 and enter with the workbook closed and the value didn't change. I
assumed (wrongly) that it would recalc.

I like your =sumproduct() formula more and more!

But you could use an array formula, too:

=SUM(IF('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$25="food city",
'C:\My Documents\excel\[book1.xls]Sheet1'!$B$1:$B$25))
(all one cell)

But instead of just hitting enter, hit ctrl-shift-enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(But I don't see a significant difference between the two formulas--the two that
work that is <bg>.)

===
And I should have known better--there was a similar question recently that I got
right (the first time!).



David said:
Dave Peterson wrote
This worked ok for me:
=SUMIF([book1.xls]Sheet1!$A$1:$A$25,"food
city",[book1.xls]Sheet1!$B$1:$B$25)
Click to expand...

Oops! Results in #VALUE if "book1.xls" not open. Mine worked if source book
was closed.
Click to expand...
 
Peter Atherton wrote
This works for a fixed range

=SUMIF([Book1]Sheet1!$K$6:$K$8,"Food City",[Book1]Sheet1!
$M$6:$M$8)
Click to expand...

As reported to Dave Peterson, results in #VALUE if Book1 is closed.
My solution:

=SUMPRODUCT(('C:\DATA\EXCEL\[FOODCOST.XLS]Nov'!$M$5:$M$34)*('C:\DATA\EXCEL
\[FOODCOST.XLS]Nov'!$K$5:$K$34="Food City"))
 
Dave,

Thanks for jumping in, anyway. You have provided valuable help to me on
many a project. Sometimes I'm able to fumble around and do it on my own,
but not often.
 
Back
Top