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

 

[barry houdini]

This part.....

'Workbook A'!B$1:B$5=A1

returns an array of TRUE/FALSE values, but for SUMPRODUCT to work as required we need these to be numbers, +0 converts the TRUE/FALSE values to 1/0 values which SUMPRODUCT can use.

Other co-ercers are available, basically any mathematical operation that doesn't change the value, like adding zero or multiplying by 1, the most common you'll see is probably this one

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

see here for a lot more on SUMPRODUCT

 

[kelvin2088]

Thanks Alot !

This part.....

'Workbook A'!B$1:B$5=A1

returns an array of TRUE/FALSE values, but for SUMPRODUCT to work as required we need these to be numbers, +0 converts the TRUE/FALSE values to 1/0 values which SUMPRODUCT can use.

Other co-ercers are available, basically any mathematical operation that doesn't change the value, like adding zero or multiplying by 1, the most common you'll see is probably this one

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

see here for a lot more on SUMPRODUCT

 

[stuartmacdonald]

I'm struggling to apply SUMPRODUCT. I have a VLOOKUP that is not working.
I want to return a value where two lists have a matching value, the original list is in workbook A (B:B), the list to match (A:A) and the value to return (S:S) are in workbook B.
Needs to work when workbook B is closed....

Here is my VLOOKUP....

=IFERROR(VLOOKUP(B2,'https://cles-my.sharepoint.com/personal/stuartmacdonald_cles_org_uk/Documents/Order book/Timesheets/[Timesheet_TG.xlsx]Projects_TG'!$A:$S,19,FALSE),0)

Any ideas how I convert this into SUMPRODUCT????