Adding totals to new sheet

[Doug]

I have a workbook with 7 sheets. Each sheet has several colums with each
having a dollar total and quanity ordered. How do I make all the totals from
each sheet total up on the last sheet for a grand total? I need to know how
many dollars were spent and how many of each product was ordered.

Doug

 

[Guest]

=SUM(Sheet1:Sheet7!A20)

 

[Doug]

Each sheet is named, Main, Flag, Midgets, Rookies, Juniors, Seniors and
Cheer. I want their totals on the Main sheet. I am tracking my youth
football teams fundraising. Should I rename them back to sheet 1,2,
etc..........

 

[Pete_UK]

The syntax is essentially first_sheet:last_sheet, so you could make it
Main:Cheer if these are the outer sheets of the range of sheets to
want to add from - ensure that the main sheet is to the left or right
(i.e. outside of the range of sheets in your file).

Hope this helps.

Pete

 

[Doug]

I renamed the sheets to sheet 1 and so on. Works great.
Thanks

 

[Gord Dibben]

Doug

Insert a dummy sheet before all sheets....name it First

Insert a dummy sheet after all sheets....name it Last

Formula is then =First:Last!A20

Any new sheets can be inserted between First and Last like the jam in a
sandwich.


Sheet names are then inconsequential.


Gord Dibben MS Excel MVP

 

[Doug]

That worked........ One more question. Need to multiply a number in a colum
times a dollar amount. Like quantity of 126 x $5.00 = ?

 

[Gord Dibben]

Assuming dollars are in column A

In B1 enter =A1*126

Or if dollars in column A and quantities in column B enter in C1 =A1*B1

Or if the multiplier for all dollars is in a cell use =A1*$D$1 where D1 is 126

Copy any of those down by dragging on the fill handle(small black square at
lower right corner of the cell.

Note the $ signs in some references and not in others.

No $ signs means that cell reference will increment as copied.

The $ sign will fix the cell reference so's it doesn't change.


Gord

 

[Doug]

That worked great. I know I said that was my last question. But now you got
me on a role. Now I want to subtract.

 

[Gord Dibben]

=A1-B1

Not to dissuade you from asking questions here but there are some pretty good
basic Excel tutorial sites on the 'net which may help you speed up the learning
curve.

http://www.usd.edu/trio/tut/excel/index.html

http://www.baycongroup.com/el0.htm

http://office.microsoft.com/en-us/training/CR061831141033.aspx


Gord