Can "SUMIF" be used across multiple workbooks?

kelvin2088

Board Regular
Joined
Mar 11, 2010
Messages
78
I have 2 workbooks, workbook A and workbook B.

Workbook A contains date, product Name and profit associated with that product. look like this:

Date Product Profit
1/1/2010 Apple $1
1/1/2010 Banana $2
1/2/2010 Apple $3
1/3/2010 Apple $4
1/5/2010 Banana $3


Workbook B contains product name and Total profit of each product, like this:

Product Total Profit
Apple $8
Banana $5


I'm just wondering can "sumif" function manage multi-workbook calculation? (I tried, and it returned me error)

If not, is there any alternative to that function /or formula? Thanks!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Re: Can "SUMIF" be used across multiple workbook?

It can work across workbooks. Can you show us your formula? What error do you get exactly?
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

Here is my formula:

=SUMIF('C:\Documents and Settings\Guest\Desktop\[Hedging Report.xls]EURDetail'!H:H, A48, 'C:\Documents and Settings\Guest\Desktop\[Hedging Report.xls]EURDetail'!N:N)

error I got here:#VALUE



It can work across workbooks. Can you show us your formula? What error do you get exactly?
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

That's odd. I have 2 workbooks open and I can do a sumif in one that has the data being summed in the other without any issues.

Which version of Excel are you using?
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

I'm using Excel 2007, but I havent tested it.
I may be interpreting "multiple workbooks" incorrectly.
Best solution is to test
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

Thank you Special.
This time it doesn't give me error message, but "0" instead. Whichis obviously wrong:confused:


That's called a 3D range and SUMIF cannot be used across multiple workbooks.
http://www.ozgrid.com/forum/showthread.php?t=27306

Try in Workbook B cell B1

=SUMPRODUCT(('Workbook A'!B$1:B$5=A1),('Workbook A'!C$1:C$5))
and copy down column B
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

I'm using Excel 2003

That's odd. I have 2 workbooks open and I can do a sumif in one that has the data being summed in the other without any issues.

Which version of Excel are you using?
 
Upvote 0
Re: Can "SUMIF" be used across multiple workbook?

What's your new formula look like? Also, if you just do a Sum (not SUMIF) on Column N in the second worksheet, what value do you get?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top