How to sum a cell across several worksheets using indirect cell reference

Posted on


Assume, I have Worksheets A-Z.

I want to SUM cell, C21, across several worksheets (e.g. A-Z).

I’ve tried this formula:


However, now if I add rows in sheets A-Z, that moves the values of C21 to C24 the formula stays the same, i.e. the formula stays SUM('A:Z'!C21), whereas it should now be SUM('A:Z'!C24).

I believe I can solve this with an indirect cell reference. Something like:


However, this returns an error (“There is a problem with this formula. Not trying to type a formula?….”).

Does anybody know why? And is the right approach or is there a better approach?


This simple Formula can solve your issue:


Or, if you want to use INDIRECT with SUM
then do the following:

  • Anywhere in Sheet, enter Sheet name and
    put one heading like in my sample data
    below is, SheetName.

    enter image description here

  • Select Entire Range including Header, and
    from Formula Tab, click Create from
    and select Top Row option,
    finish with Ok.

enter image description here

  • Enter this formula in any blank Cell


Adjust Sheet Name, Cell references and Range Name in Formula as needed.

Leave a Reply

Your email address will not be published. Required fields are marked *