Formula to tally totals from different sheets WAS working........

[MomoHime]

I was using a fairly simple formula to keep track of running totals that are recorded weekly for a company contest I'm running. Each week belongs to a separate sheet, and then I have a TOTALS sheet where I add the values from each one. The formula below works perfectly fine, but when I tried to update it with Week8 totals, Excel gave an invalid cell reference error message.

Working formula:

=SUM(Week1!F21,Week2!F21,Week3!F21,Week4!F21,Week5!F21,Week6!F21,Week7!F21)

New formula that DOESN'T work:

=SUM(Week1!F21,Week2!F21,Week3!F21,Week4!F21,Week5!F21,Week6!F21,Week7!F21,Week8!F21)


If anyone could help me understand why this is not working, that would be great. I have fiddled around with it for a while now and I'm getting nowhere.

Oh, well. AT LEAST IT'S FRIDAY! :D

[daffodil11]

Looks fine to me, but it's hard to tell without an attachment.

My guess is that one of the sheets doesn't exist, or is mispelled. If you put a space between Week and 8 on your final tab, it would need to be 'Week 8'!F21.

Also doublecheck to make sure there isn't a space after the 8, such as 'Week8 '!F21.


If you're still stumped, just click around manually.

Type =SUM( then click Week1 tab, go to F21 and click it, hit comma, and repeat for each tab as needed.

[MomoHime]

There was no space, but for some reason putting the single quotes around Week8 makes it work! I also changed the commas to plus signs to simplify.

Thanks for the help!

[Jonmo1]

Changing the commas to plus signs doesn't simplify it. Actually quite the opposite.
It actully adds work that Excel must do, AND makes it more open to errors.

It adds work because the math is done twice.
Once by the + symbols, then again by the Sum Function.
FYI, the SUM function will only really sum 1 value, the result of a+b+c+d

It can cause errors because
SUM(A1+B1) will result in an error if either A1 or B1 is a text string
But SUM(A1,B1) will Not


Hope that helps.