Can "SUMIF" be used across multiple workbooks?

[kelvin2088]

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

 

[rdw72777]

I'm told Excel 2003 can't handle the whole column reference. Change H:H to H1:H65535 and N:N to N1:N65535. If that doesn't work, maybe named ranged could help you use the whole column.

 

[kelvin2088]

Let me try.
Btw, what's the correct formula for sumproduct? how come I get 0 for using sumproduct function?

I'm told Excel 2003 can't handle the whole column reference. Change H:H to H1:H65535 and N:N to N1:N65535. If that doesn't work, maybe named ranged could help you use the whole column.

 

[rdw72777]

I think Sumproduct and Sumif would have the same issues regarding entire column rnages. try the Sumif with the modified ranges, if that doesn't work we can try a modified SUmProduct or an array formula. this is definitely possible.

 

[kelvin2088]

Bad news is that it still doesn't work
goodnews is that I'm working on an alternative: I sumproduct on workbook A first then vlookup workbook A from workbook B

I think Sumproduct and Sumif would have the same issues regarding entire column rnages. try the Sumif with the modified ranges, if that doesn't work we can try a modified SUmProduct or an array formula. this is definitely possible.

 

[rdw72777]

I really think something is in the data, but if it works in book A then that's not the case. I can't replicate your issue unfortunately.

 

[kelvin2088]

I appreciate your help anyway.....looks like I'll just use vlookup to coverup this issue for now

I really think something is in the data, but if it works in book A then that's not the case. I can't replicate your issue unfortunately.

 

[RoryA]

FYI, SUMIF will work across workbooks and will accept an entire column as a reference, but the source workbook has to be open.
SUMPRODUCT will not work (pre-2007) with an entire column.

 

[barry houdini]

what's the correct formula for sumproduct? how come I get 0 for using sumproduct function?

Special-K99's formula is missing a "coercer", try like this

=SUMPRODUCT(('Workbook A'!B$1:B$5=A1)+0,('Workbook A'!C$1:C$5))

 

[kelvin2088]

Great Great Great!!
Now it works!!
Thanks!!

Special-K99's formula is missing a "coercer", try like this

=SUMPRODUCT(('Workbook A'!B$1:B$5=A1)+0,('Workbook A'!C$1:C$5))

 

[kelvin2088]

btw, what does that "+0" mean?

Special-K99's formula is missing a "coercer", try like this

=SUMPRODUCT(('Workbook A'!B$1:B$5=A1)+0,('Workbook A'!C$1:C$5))