Need to condense my formula summing many values over many sheets
 

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

Need to condense my formula summing many values over many sheets
 

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


"Andy777" wrote:

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

Need to condense my formula summing many values over many sheets
 

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



"Andy777" wrote:

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

Need to condense my formula summing many values over many sheets
 

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

Need to condense my formula summing many values over many sheets
 

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

Need to condense my formula summing many values over many shee
 

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

Vaya con Dios,
Chuck, CABGx3



"Andy777" wrote:

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