How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

=SUM(A2:OFFSET(A2,MATCH(999999,A:A,1),1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Anthony B" wrote in message
...
How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

Anthony B:

Another approach would be to define a name for your range, for example
QuickSum. After you do this, the named range would be available in the Name
Box (which is immediately above column A. When you select that named range,
you should see the sum of selected cells in your status bar (the very bottom
of the spreadsheet). I use 2007 and by right-clicking this status bar, the
Customize Status Bar window appears and mine shows that Average, Count, and
Sum are checked. Whenever I select a range, the status bar automatically
shows Average, Count, and Sum.

Of course, if you type that formula =sum(A1:A15) in a convenient cell, say
B1, you only have to do it once and the sum will be there. Therefore you
don't have to " ALWAYS have to set up formula =sum(A1:A15) to get the sum" as
long as you save the worksheet.

"Dr Teeth" wrote:

On Wed, 2 Jan 2008 17:36:23 -0600, "Don Guillett"
wrote:

=SUM(A2:OFFSET(A2,MATCH(999999,A:A,1),1))

Can you explain how that works? That way this newuser would be able to
learn how the command works and not have to ask again for a different
range.

Cheers,

Guy

** Stress - the condition brought about by having to
** resist the temptation to beat the living daylights
** out of someone who richly deserves it.

In Namebox type A1:A115 then hit Enter.

Right-click on status bar and "Sum"


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 14:51:02 -0800, "Anthony B" wrote:

How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

A couple of other ways.

1
To sum A1:A115
In A116 press Alt and = together
let go then hit enter

2
Highlight A1:A115
And look at the status bar at the bottom right of your screen
You may have to right click on the status bar and check sum first.

HTH
Martin



"Anthony B" wrote in message
...
How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

It is self adjusting using match to find any number greater than possible in
your column. As you add/delete rows it will adjust the sum accordingly.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dr Teeth" wrote in message
...
On Wed, 2 Jan 2008 17:36:23 -0600, "Don Guillett"
wrote:

=SUM(A2:OFFSET(A2,MATCH(999999,A:A,1),1))

Can you explain how that works? That way this newuser would be able to
learn how the command works and not have to ask again for a different
range.

Cheers,

Guy

** Stress - the condition brought about by having to
** resist the temptation to beat the living daylights
** out of someone who richly deserves it.

Anthony B wrote:
How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

I see you have several suggestions but for me the quickest is select
A1 then move your cursor to the bottom of the cell and you will get a
symbol with 4 points, hold the shift key and double click. This will
highlight A1 through A115 then hit the sum icon. It's the symbol that
looks kind of link an E(sigma sign).

gls858

"gls858" wrote in message
...
Anthony B wrote:
How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

I see you have several suggestions but for me the quickest is select
A1 then move your cursor to the bottom of the cell and you will get a
symbol with 4 points, hold the shift key and double click. This will
highlight A1 through A115 then hit the sum icon. It's the symbol that
looks kind of link an E(sigma sign).

gls858

I am using Excel 2007 of the Microsoft Office 2007.
Where is the sum icon?

Is there anyway I can exclude a few cells in this entire column?
For example, sum of A1:A70 + A71:115.

"
gls858

I am using Excel 2007 of the Microsoft Office 2007.
Where is the sum icon?

Is there anyway I can exclude a few cells in this entire column?
For example, sum of A1:A70 + A71:115.


ok, I found it. Its in the bottom right corner,
showing sum, average and count.

Your example shows no excluded cells.

For example, sum of A1:A70 + A71:115

which is the same as A1:A115

If you meant A1:A70 + A75:A115 yoi will have to use CTRL + Click and drag to
select the two distinct ranges then look on the status bar.


Gord Dibben MS Excel MVP

On Thu, 3 Jan 2008 15:51:37 -0800, "Anthony B" wrote:

"gls858" wrote in message
...
Anthony B wrote:
How to quick calculate sum of colume A1 to A115?

Or, do I always have to set up formular =sum(A1:A15) to get the sum?

I see you have several suggestions but for me the quickest is select
A1 then move your cursor to the bottom of the cell and you will get a
symbol with 4 points, hold the shift key and double click. This will
highlight A1 through A115 then hit the sum icon. It's the symbol that
looks kind of link an E(sigma sign).

gls858

I am using Excel 2007 of the Microsoft Office 2007.
Where is the sum icon?

Is there anyway I can exclude a few cells in this entire column?
For example, sum of A1:A70 + A71:115.

Anthony B wrote:
"
gls858

I am using Excel 2007 of the Microsoft Office 2007.
Where is the sum icon?

Is there anyway I can exclude a few cells in this entire column?
For example, sum of A1:A70 + A71:115.

ok, I found it. Its in the bottom right corner,
showing sum, average and count.

Actually I was talking about the icon in the editing box on the home
tab. It will insert the sum formula for that column after the last entry
in the column.

gls858