Excel Question, Totalling all formulas on a sheet?

[Pemolis]

I am doing some data analysis (work project). I have 1 main worksheet with all the information, and for each snippit of info,I did a formula to add up everything.

Closest analogy to this would be checks. I have about 30 businesses I write checks out to.. I put the business name, the check # and the check amount. I have about 600 or so checks on this one excel sheet.

For each business, I did an autosum. Now I have 30 autosums across this one sheet. Is there some method of adding up every autosum (or formula) across this sheet to get a grand total?

if that is not possible, is there a way to add up every Currency Value Cell on a sheet.

Thanks.

[mcs328]

I don't understand what autosum is. It sounds like you got a subtotal based on business and you want a grand total on everything. Why can you sum on the check amount column? For example in Cell C601 =SUM(C1:C600)

[cruelpupet]

I don't understand what autosum is. It sounds like you got a subtotal based on business and you want a grand total on everything. Why can you sum on the check amount column? For example in Cell C601 =SUM(C1:C600)


That assumes he doesnt have the total in the same column as the checks.

Is it too much to manually type =SUM(C1,C10,C27,etc) where all those values are the subtotals?

[cruelpupet]

Or possibly even better.

Make the first sheet just subtotals and grand total, and each sheet after that is for each business

So for the first sheet youd have:

Microsoft Subtotal: =Sheet2!SUM(D3:D100) where D is the check column
Novell Subtotal: =Sheet3!SUM(D3:D100)
Cisco Subtotal: =Sheet4!SUM(D3:D100)

Grand Total: =SUM(B2:B5) where B='s the subtotal column above

[cruelpupet]

And where the stupid smiley is actually a colon and the letter D

[YellowCoffee]

Would the subtotal(9,range) function help? I'm not sure I understand exactly what you're asking for. Could you explain a bit more?

[mcs328]

Oh I think I see...

Yahoo | Check # 1 | $100
Yahoo | Check # 2 | $100
Autosum |blank | $200
Google | Check # 3 | $300
Google | Check # 4 | $100
Autosum |blank | $400
Total |blank | $600

Is that how your excel is setup?

[attgig]

I say, get rid of the autosum and use a pivot table.

the pivot table will give you all the sums that you need solely based on the checks.

throw the $ (and make sure you have it as sum) in the data part.
the x, throw in the payee.
if you want to see every row, then throw in the check number into the x axis as well.

just a cleaner way to display sums like you want to see.


it must have been a pain to throw in the autosums like the way you did.... but get rid of it. just adds confusion into the spreadsheet.

[YellowCoffee]

Oh I think I see...

Yahoo | Check # 1 | $100
Yahoo | Check # 2 | $100
Autosum |blank | $200
Google | Check # 3 | $300
Google | Check # 4 | $100
Autosum |blank | $400
Total |blank | $600

Is that how your excel is setup?

IF this is how it's setup, you'd have to make your autsums subtotals instead using the subtotal(function_num,range) function. Then you can use a subtotal of the entire list to get the grand total.

Otherwise, you can create a pivot table as someone had mentioned.

[mcs328]

Yeah I'd go with Pivot tables or a separate worksheet for each business.

[mcs328]

http://www.youtube.com/watch?v=7zHLnUCtfUk
Here's a good low res video tutorial on pivot tables.

[Pemolis]

sorry for the delay.

Actually figured out how to constantly retotal amounts based on the background color of the cell.

I make all the cells I want to addup Brown

Fast to keep track, and updates itself if I add a ton more information as long as I make the cell I want totalled Brown (or whatever color I designate).