Sum across different worksheets

[Josh]

What is the correct syntax for summing the same cell from
several different worksheets? The syntax I used is:

=SUM(Sheet1!BF78:Sheet35!BF78)

I am receiving an #VALUE! error message but shouldn't this
formula work to sum cell BF78 for each of the 35
worksheets?

 

[J.E. McGimpsey]

Try:

=SUM('Sheet1:Sheet35'!BF78)


The 's aren't strictly necessary if your sheet names don't have
spaces in them.

Note that the SUM works BY POSITION not sheet title - if Sheet2 is
to the left of Sheet1, it will not be included in the SUM. For taht
reason, some people insert two empty sheets at the extremes of the
sheets they want to sum, named something like FirstSheet and
LastSheet. Then the sum becomes:

=SUM('FirstSheet:LastSheet!BF78)

so that sheets can be added, deleted or renamed in between and the
sum will still be valid.