HELP!!! Not sure how to phrase this into a question ..

A

andy

I'm working on a huge excel spreadsheet that has numbers in columns that must
be added. However, because this spreadsheet is constantly sorted by various
variables, I'm unable to use the Sum() function to add these - and thus have
resorted to looking at the taskbar to find the total and entering it in
manually. So for example I'll have a column of: 4, 5, 9... and rather than
using the sum function to add these ... I'm manually typing in 18 directly
below these #'s.

My question is ... is there a way to get the answer to the sum () function
to automatically paste itself into the cell directly below these #'s?
 
G

Gord Dibben

Why have it below the the numbers?

Have your SUM formula placed in the top row which does not sort because you have
selected "my data range has a header row"

=SUM(A:A) or similar.


Gord Dibben MS Excel MVP
 
A

andy

It's just the way my workplace has it setup.... I can't really change it
since there are over 100,000 different cells on this thing. If I were to
sort the spreadsheet with the sum function attached to these #'s, the sum
would be incorrect - since the cells that make up the sum() would be
different numbers .... u know what I mean?

So how do I get the sum answer to be directly entered into the cell without
having to manually type it in?
 
A

andy

how about this ...

When I highlight these cells... the sum() function shows up with the answer
in the task bar ... how do I get this answer to be directly pasted below
these cells without using the sum (i.e. so that it's entered as "36" as
opposed to sum(E1:E7) )
 
G

Gord Dibben

=SUM(A:A) doesn't care how the cells are sorted.

You are summing a range of numbers, not individual numbers.

Without typing it in would require VBA.

Copying from the Sum on Status bar is not an option AFAIK


Gord Dibben MS Excel MVP
 
G

Gloria Thrurk

Why not use a pivot table to sum the data?
Set up the pivot table to read your sheet as external data and when you save
it XL will ask you if you want to save it as an emty file.
If you reply Yes, then XL will re-read the external data (xl) file next time
you open the pivot table and read all the data even if the length of the
columns has changed.

G.
 
Top