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

Posted on

QUESTION :

Assume, I have Worksheets A-Z.

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

I’ve tried this formula:

=SUM('A:Z'!C21)

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:

=SUM('A:Z)'!(Indirect(CEll("address,C21))))

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?

ANSWER :

This simple Formula can solve your issue:

=SUM(B:Z!C21)

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
    selection
    and select Top Row option,
    finish with Ok.

enter image description here

  • Enter this formula in any blank Cell

    =SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))

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 *