need sum from another workbook

[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?

 

[Dave Peterson]

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.

 

[Peter Atherton]

David

This works for a fixed range

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

Regards
Peter

 

[David Turner]

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.

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"))

 

[David Turner]

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)

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

 

[Dave Peterson]

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!).

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)

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

 

[David Turner]

Peter Atherton wrote

This works for a fixed range

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

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"))

 

[David Turner]

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.