Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 2:40"))&"'!A:A"),A1,INDIRECT("'Sheet"&ROW(INDIRECT ("2:40"))&"'!F:F")))

Sums column F on sheets 2 through 40 if column A in sheets 2 through 40
contains the value in A1.

Biff

"Andy777" wrote in message
...
I'm having trouble finding a way to condense my formula which searches up
to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way
to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew