how to get weights of numbers in excel in this problem

Question

in excel i have a column which contains about 5 numbers

    cola
    num1
    num2
    num3
    num4
    num5

now i have another excel file which has two columns and three numbers

cola  colb
num1  5
num4  4
num2  1

and another excel file which has two columns and three numbers

cola   colb
num2   2
num3   6

how can i use excel formula so that in using the 3 excel sheets above i should get the output sheet with two cols

num1  5
num2  3  ( 1 + 2 )
num3  6
num4  4
num5  0

Any help appreciated.

Answer 1

Without specific cell, sheet, and workbook references references, you'll have to do some work to translate this, but:

Open all the books, then in Book1 in B1, put this formula

=SUMIF([Book2.xls]Sheet1!$A$1:$A$3,A1,[Book2.xls]Sheet1!$B$1:$B$3)+SUMIF([Book3.xls]Sheet1!$A$1:$A$2,A1,[Book3.xls]Sheet1!$B$1:$B$2)

and fill down.