Aggregate a range of worksheets with errors

Question

I have several worksheet in Microsoft Excel and I am currently adding them like this:

=SUM(MySheet1:MySheetN!A2)

This funtion adds the content of all A2 cells on all the worksheets contained between MySheet1 and MySheetN both included. If I have one single error the sum results in an error. I see that there is an auxiliar AGGREGATE funtion that allows to ignore errors.

 =AGGREGATE(9,6,MySheet1:A2,MySheet2!A2, ....,MySheet2!AN)

This works but I need to specify all the sheets. If I add a new worksheet I need to modify all the formulas. How could I solve this issue to make it generic?

Note: I am using the spanish version. I have tried to translate function name and syntax but might have forgot something.

Answer 1

unfortunately, "If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value."

It sounds like u would need to first copy the cells to a single worksheet, then use the AGGREGATE function.

Also

"Type of Range:

The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate."

So make sure u copy the cells into colums and not rows.