sum same cell across many sheets

Question

I need to sum all F1 cells across many sheets.  Is there a simple formula like there is to sum an entire column?  OR do I need to include each tab name in the formula?

Answer 1

Just use the tab names as you would to sum a column

Example, my first sheet is called Summary, my last sheet is called Data and I could have many other sheets in between.

=SUM(Summary:Data!F1)

Answer 2

I should now also add that when I had multiple sheets highlighted and added the formula to sum the entire column of F with the above mentioned formula, it returned the correct sum.  BUT when I did an F2 on the cell and hit enter, the value changed to zero.  So, it must be canceling the formula due to the circular reference caused by having the cell within the formula.  This is what must be causing my sum of all F1's to be zero.

Any insight would be appreciated!  :)

Answer 3

Yes, thank you all!  I have tried this, but for some reason, it is returning zero as my answer.  Here is my formula exactly:

=SUM('NP0:NP6'!F1)

Looks right, but not sure what's happening.  Am I missing an add on?  I will also state that each f1 has the following formula:

=SUM(F:F)

Which, to me, shouldn't matter - but getting all info to the experts.

Thanks!

Answer 4

Hi,

If the sheets are in sequence

=SUM(Sheet1:Sheet3!F1)

Regards

JY

Answer 5

You can sum the same cell across multiple worksheets with a spanning cell reference.

=SUM('Sheet2:Sheet5'!F1)

That will SUM() F1 for Sheet2, Sheet3, Sheet4 and Sheet5. If you wanted to miss Sheet6 and add Sheet7 then it would be,

=SUM('Sheet2:Sheet5'!F1,Sheet7!F1)