excel sum functin

  • Thread starter Thread starter toby
  • Start date Start date
T

toby

hi.
i 've a column of data whcih is the quantity of each items. how can i fix
the area of sum function (ie. $B$12 - $B$32)? so when i insert new row of
data, the sum area would change to (B12-B33) automatically?
thx in advance

Toby
 
Hi Toby!

One way:

=SUM(OFFSET($B$12,0,0,COUNTA($B:$B)))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Toby,

In cell B34, insert the following formula.

=SUM(B13:OFFSET(B34,-1,0))

When you add an incremental row prior to the sum, the formula will capture
the added row.

Hope that helps.

Best regards,
Kevin
 
Toby,

That should be

In cell B33, insert the following formula.

=SUM(B13:OFFSET(B33,-1,0))
 
Hi Norman,

I tried your solution and got a circular error when I entered that formula
in C33. Your formula works if the sum is in a different column other than
Col B. I *think* a slight modification would allow the sum to be placed
below the data in cell C33.


=SUM(OFFSET($B$12,0,0,COUNTA($B:$B)-1))

If I have goofed, please correct.

Best regards,
Kevin
 
Sorry Kevin,
i should modify my question because of typing error
wht i want to do is to *stop* excel from changing the area automatically.
it's exactly an opposite to the situation i mention before.
 
Hi Toby,

If you want to have a *fixed* area that does *not* change, then

==SUM(OFFSET(B12,0,0,21)) you can enter this formula anywhere on the
spreadsheet. 21 is the number of rows B12 thr to B32 inclusive.

If you want to have a *flexible* area that does change as you add rows, then

=SUM($B$12:OFFSET($B33,-1,0)), entered in cell B33 OR
=SUM(OFFSET($B$12,0,0,COUNTA($B:$B))) entered in a different column other
than B.

Hope that helps.

Regards,
Kevin
 
=SUM(OFFSET(B12,0,0,21)) you can enter this formula anywhere on the
spreadsheet. 21 is the number of rows B12 thr to B32 inclusive.
Click to expand...
...

This doesn't prevent changes when rows are inserted above row 13. If the OP
always wants the sum of B12:B32, the simplest way is using

=SUM(INDIRECT("B12:B32"))
 
Harlan Grove said:
...
..

This doesn't prevent changes when rows are inserted above row 13. If the OP
always wants the sum of B12:B32, the simplest way is using

=SUM(INDIRECT("B12:B32"))
Click to expand...

Hi Harlan,

I'm confused.

How does this meet the OP's original request??

" how can i fix the area of sum function (ie. $B$12 - $B$32)? so
when i insert new row of data, the sum area would change to (B12-B33)
automatically?
thx in advance"

Seems Toby WANTS to migrate from 32 to 33 whenever a row is iserted.

Regards

David
 
...
I'm confused.

How does this meet the OP's original request??

" how can i fix the area of sum function (ie. $B$12 - $B$32)? so
when i insert new row of data, the sum area would change to (B12-B33)
automatically?
thx in advance"

Seems Toby WANTS to migrate from 32 to 33 whenever a row is iserted.
Click to expand...

If you had the most miniscule fragment of intellect you could attempt to read
the *ENTIRE* thread, in this case the OP's follow-up,

http://www.google.com/[email protected]

in which he states:

"Sorry Kevin,
i should modify my question because of typing error
wht i want to do is to *stop* excel from changing the area automatically.
it's exactly an opposite to the situation i mention before."

You could check that my response is in response to this follow-up. However, it's
becoming evident that the assumption that you possess any intellect or even
rudimentary cleverness whatsoever is just plain wrong. Vacuous seems to sum up
neatly your droppings in this newsgroup.
 
Harlan Grove said:
If you had the most miniscule fragment of intellect you could attempt to read
the *ENTIRE* thread, in this case the OP's follow-up,

You could check that my response is in response to this follow-up. However, >it's becoming evident that the assumption that you possess any intellect or >even rudimentary cleverness whatsoever is just plain wrong. Vacuous seems to >sum up neatly your droppings in this newsgroup.
Click to expand...

Well now:

I *did* say that I was confused, and I clearly had *not* read the
reversal, but WOW.

But what a response.

Surely a simple "The OP reversed his requirements in a later post"
would have sufficed.

As to my intellect or cleverness, I rely on the opinions of those I
respect.

As to the content of my responses, if they really *are* so vacuous,
what does it say of *your* intellect that you rise with such
enthusiasm to consume my "droppings"?

David
 
I *did* say that I was confused, and I clearly had *not* read the reversal,
Click to expand...
...

So why did you respond at all? Confusion is often relieved by reading *ENTIRE*
threads. Try it sometime.
 
Back
Top