Lookup and return
 

How do I write a formula in one worksheet that looks in many other worksheets
and returns the sum of all the values in the last-used cells in a certain
column?

Delia wrote...
How do I write a formula in one worksheet that looks in many other worksheets
and returns the sum of all the values in the last-used cells in a certain
column?

If these columns had no blank cells from row 1 to the last row with
data, you could try something like

=SUMPRODUCT(N(INDIRECT("'"&{"Sheet2";"Sheet3";"She et4"}&"'!A"
&COUNTIF(INDIRECT("'"&{"Sheet2";"Sheet3";"Sheet4"} &"'!A:A"),"<"))))

You can adapt this for known numbers of blank cells in the column in
question, but a general solution with varying numbers of blank cells
would require formulas in multiple cells to produce this one result or
VBA.

Thank you!

"Delia" wrote:

How do I write a formula in one worksheet that looks in many other worksheets
and returns the sum of all the values in the last-used cells in a certain
column?