Try...

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("2 :20"))&"!A1:A100"),Shee
t1!A1,INDIRECT("Sheet"&ROW(INDIRECT("2:20"))&"!E1: E100")))

Note that ROW(INDIRECT("2:20")) refers to Sheet2 through Sheet20.
Change this reference accordingly.

Hope this helps!

In article ,
"Noemi" wrote:

I have a workbook which contains about 20 worksheets.

In sheet1 I have a list of numbers which could be also on the other 19 sheets.

What I need to do is look at each of the other 19 sheets to see if they have
the number which is on sheet1 and if so I need to get the numerical data in
the 5th column to be returned and added together.

ie
sheet1 a1 = 2001

sheet4 a5 = 2001 e5 = 2
sheet6 a21 = 2001 e21 = 5

therefore sheet1 b1 = e5 + e21 = 7

Hopefully someone could help me.

Thanks
Noemi