# 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?

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.

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

• 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.