PDA

View Full Version : Excel Question, Totalling all formulas on a sheet?

Pemolis
10-26-2010, 08:03 AM
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
10-26-2010, 01:05 PM
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
10-26-2010, 03:54 PM
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
10-26-2010, 03:59 PM
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
10-26-2010, 04:00 PM
And where the stupid smiley is actually a colon and the letter D

YellowCoffee
10-27-2010, 08:38 AM
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
10-27-2010, 09:51 AM
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
10-27-2010, 10:13 AM
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
10-27-2010, 11:19 AM
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
10-27-2010, 12:37 PM
Yeah I'd go with Pivot tables or a separate worksheet for each business.

mcs328
10-27-2010, 12:48 PM