Adding Multiple Worksheets

[grumblenoise]

Hi Guys,

I hope someone can solve this one for me. I have 6 worksheets (which
will grow to about 20) which I want to add together to form a total in
my totals worksheet. So I have:-

Wk1
Wk2
Wk3
Wk4
Wk5
Total

The formula I have used in my totals worksheet is:-

=SUM('Wk1:Wk6'!C5:F200)

This works but the total is double, so if I put 1 in Wk1 as a test, I
get 2 in the total page. I think it's something to do with the fact
that's it's adding itself again but I'm not sure why. Can someone
help me and show me the light!?

Thanks a lot.

 

[Dave Peterson]

Is the total worksheet outside the "sandwich" of other worksheets?

If it is not, then move it to the far right or far left.

If it already is, then maybe you have =subtotal()'s in those C5:F200 cells in
wk1:Wk5. =Sum() will include both the original values and the =subtotal()'s.

ps. I like to create a couple of worksheets (First and Last) and put them to
the left and right of the worksheets with the data. Then my formula would look
like:

=sum('first:last'!c5:f200)

And I can add new sheets within this sandwich--or play what-if games by moving
sheets outside the sandwich.

 

[Gord Dibben]

Don't include the Totals sheet in your calcs.

=SUM(Wk1:Wk2!C5:F200) in Wk6

To account for addintional sheets you would be better off to have Totals sheet
first in book then create a dummy sheet after that. Name it Start

Create another dummy sheet at end and name it End

In Totals sheet enter =SUM(Start:End!C2:F200)

Insert new sheets between Start and End sheets.


Gord Dibben MS Excel MVP

 

[grumblenoise]

OK, I created a Start and Last sheet, in between my Weeks sheets, I
then set the sum to:-

=sum('first:last'!c5:f200)

It worked again but it's still doubling the totals. I don't
understand why it's doing that... Grrrrrrr.

 

[Dave Peterson]

Do you have sums/subtotals on those other sheets that are being included?

 

[grumblenoise]

Nope, nothing on any of the other sheets.....

I could upload it maybe and someone could take a look?

 

[Dave Peterson]

There are free file hosting sites on the web:

http://www.savefile.com

is one of them.

 

[grumblenoise]

Thanks mate, here is the link.

http://www.savefile.com/files/1604803

 

[Dave Peterson]

I didn't see any formulas on the Totals sheet that pointed back to previous
sheets.

But if you're adding columns C:F of every sheet and columns C:E contain data and
column F contains the sum of columns C:E, you're going to get twice the total.

Don't include column F.

 

[grumblenoise]

Yes, I didn't put the sum but I was using one above. Based on what
I've told you and what you are looking what formula is needed?

Cheers.

 

[Dave Peterson]

Something like:

=SUM('first:last'!c1:e200)
to total the individual entries

or if your totals are right in column F of each sheet
=sum('first:last'!f1:f200)

Remember that these ranges can't include grandtotals (like at the bottom of each
column). Otherwise, you'll end up with more than you want.