Adding values in multiple columns matching some criteria

[jppe]

Hi helping guys...
I have a rather unusual situation. I have two excel files, the firs
one has information on incoming products ordered by date and the secon
one has detailed information of each product ordered by product code
This last field (product code) is the field that allows me to link bot
files, so that I can get detailed information for each incomin
product.

Here is a scheme of what I have:
File 1
     A        B       C
1 Date ProdCode Qty
2 date1   code1   16
3 date2  code45   31
4 ...

File 2
          A        B       C
1 ProdCode Height BoxSize
2
code1      1.56    0.94
3
code2      1.92    1.06
4 ...

What I need to do is to add whatever values are in columns B and C i
the second file and send this result to a new column in the first file
but paying attention to the product codes, so that I only get th
result of the sum that corresponds to the code of each row. In th
example above, the result would be 2.5 for the first code and 2.98 fo
the second code.
I know that the easiest way (by far) would be to add a column in th
second file that adds boths values and then use VLOOKUP, because that'
the way I did it, but what I wanted to know was if there is a formul
that can be used to avoid generating that extra auxiliar column in th
second file.
Wooo... that was hard to explain... I hope I've made myself clear, i
not please let me know your doubts and I'll answer them as fast as
can.
Thanks to you all...
Juan Pabl

 

[Frank Kabel]

Hi
have a look at SUMIF in the Excel help

 

[Domenic]

Hi Juan,

D2, copied down:

=SUM(OFFSET([File2.xls]Sheet1!$A$1,MATCH(B2,[File2.xls]Sheet1!$A$2:$A$10,0),1,1,2))

Adjust the range accordingly.

Hope this helps

 

[jppe]

Thanks a lot guys! The Offset sum helped really well