Hi Biff

My worksheets have numbers as names therefore I defined then as follows:

snames = '123456:987654!$A$2:$A$180

can you please confirm this is correct as I am geeting the folloiwng error

#value!

Thanks Noemi

"Biff" wrote:

Hi!

Try this:

If your sheet names really are Sheet1, Sheet2, Sheet3, etc., ...........

This is for Sheet2:Sheet20 .........

=SUMPRODUCT(SUMIF(INDIRECT("'sheet"&ROW(INDIRECT(" 2:20"))&"'!A1:A25"),A1,INDIRECT("'sheet"&ROW(INDIR ECT("2:20"))&"'!E1:E25")))

This is equivalent to using a Sumif on each sheet:

=SUMIF(Sheet2!A1:A25,A1,Sheet2!E1:E25)

If your sheet names are something else then:

Create a list of those names. Assume that list is in the range J1:J19. Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J19&"'!A1:A25"), A1,INDIRECT("'"&J1:J19&"'!E1:E25")))

Or, give the list of sheet names a defined name:

InsertNameDefine
Name: Snames
Refers to: =Sheet1!$J$1:$J$19

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1:A25"), A1,INDIRECT("'"&Snames&"'!E1:E25")))

Biff

"Noemi" wrote in message
...
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