Need to condense my formula summing many values over many sheets

[Andy777]

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,Sheet3!$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

 

[Dave F]

I would look into using the VLOOKUP function to search on that many
conditions. Look in Excel's help for its syntax.

 

[CLR]

You might consider using a range of helper cells (12 or whatever) containing
your individual MATCH/VLOOKUP formulas and then just one SUM formula to add
them together.

hth
Vaya con Dios,
Chuck, CABGx3



hth
Vaya con Dios,
Chuck, CABGx3

 

[Biff]

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]

Thanks for that guys, really helpful. I think I'm gonna go with the helper
cells to get me a few subtotals to add together for the final total.
Andrew

 

[CLR]

You're welcome Andy............glad one of the suggestions worked for you.

Vaya con Dios,
Chuck, CABGx3