Compare and sum over two *.xls

[svgvl]

Hello,

I really hope someone can help me on this. Because this has been driving me crazy the whole day :P

I have two excel files total.xls and items.xls.
Total looks like this:
row1-> customers name, row2-> part number,row3->old part number(if available), row4-> items purchased(total of one part number and customer)
and items like this:
row1-> customers name, row2-> part number, row3-> items purchased(in a single purchase)


In items.xls are single purchases sorted by customers, part numbers and the values of the purchases, in total.xls should be displayed which customers purchased how many/how much of an item this year in total. So the formula in totals.xls compares the customers name with a range in items.xls, if it matches in one row compare the item number in the same row and if that matches as well sum up all purchased items in the rows who have this customer - part number combination.
Additionally the same for the old part numbers if they have been changed during the year, as they are combined. Somehow i can compare ranges and single cells from total.xls with a single cell out of items.xls but not with a range in items.xls.
My attempt was like this:

=SUM
(SUM
(IF
(('[items.xls]SAP Report FY0607'!$A$6:$A$3000='Total Volume FY 0607'!$A65)*('[items.xls]SAP Report FY0607'!$B$6:$B$3000='Total Volume FY 0607'!$D65),
'[items.xls]SAP Report FY0607'!$E$6:$E$3000)),
(SUM
(IF(('[items.xls]SAP Report FY0607'!$A$6:$A$3000='Total Volume FY 0607'!$A65)*('[items.xls]SAP Report FY0607'!$B$6:$B$3000='Total Volume FY 0607'!$E65),
'[items.xls]SAP Report FY0607'!$E$6:$E$3000))))

Thank you in advance,
Michael

[rylo]

MIchael

Can you zip up examples of both your workbooks, and give details on what information should come from where in your solution.

Much easier for us to understand....


rylo

[svgvl]

MIchael

Can you zip up examples of both your workbooks, and give details on what information should come from where in your solution.

Much easier for us to understand....


rylo

I cant attach the original files but i made an example which is exactly the same.
Cell D1 in total.xls should display "15" as customer 1 bought item 1 two times (10+5) and D9 should display "60" as customer 2 bought item 1 one time with the new material number(12345) and one time with the old material number(11223) = 50+10 (E9 + E18 in items.xls).

Edit:
Thanks for your concern but I think I found my mistake:
I didnt know you had to use ctrl+shift+enter for array formulas

[rylo]

HI

I'll take it that you have found your solution.

rylo