Adding Multiple Worksheets

G

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

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

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
 
G

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

grumblenoise

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

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top