New Excel function SUMABOVE

B

brent

Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
B

Biff

=SUM(D1:INDEX(D:D,3))

Is the equivalent of:

=SUM(D1:D3)

Or, using A1 to hold the variable:

A1 = 3

=SUM(D1:INDEX(D:D,A1))

Note: if A1 is empty or 0 all of column D will be summed.

Biff
 
B

Bob Phillips

=SUM(D1:OFFSET(D4,-1,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
Click to expand...
http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
 
D

Don Guillett

not automatic on row insertion

--
Don Guillett
SalesAid Software
[email protected]
Biff said:
=SUM(D1:INDEX(D:D,3))

Is the equivalent of:

=SUM(D1:D3)

Or, using A1 to hold the variable:

A1 = 3

=SUM(D1:INDEX(D:D,A1))

Note: if A1 is empty or 0 all of column D will be summed.

Biff

brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be
added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
 
B

Biff

Oh, I wasn't paying attention!

Biff

Don Guillett said:
not automatic on row insertion

--
Don Guillett
SalesAid Software
[email protected]
Biff said:
=SUM(D1:INDEX(D:D,3))

Is the equivalent of:

=SUM(D1:D3)

Or, using A1 to hold the variable:

A1 = 3

=SUM(D1:INDEX(D:D,A1))

Note: if A1 is empty or 0 all of column D will be summed.

Biff

brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be
added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
Click to expand...
 
B

brent

No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new
row is added?

I'm looking for a relatively simple solution which can be applied by people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that have
this problem, as well as some of the ugly but traditional workarounds such as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
 
D

Don Guillett

Simple you want, simple you get
Put this in a regular module> in the bottom cell just type

=sa()

Public Function sa()
Application.Volatile
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row - 1
mc = ActiveCell.Column
sa = Application.Sum _
(Range(Cells(1, mc), Cells(lr, mc)))
End Function
--
Don Guillett
SalesAid Software
[email protected]
brent said:
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a
new
row is added?

I'm looking for a relatively simple solution which can be applied by
people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that
have
this problem, as well as some of the ugly but traditional workarounds such
as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot
the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This
eliminates
issues of inserting rows immediately above the formula row and having
to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be
added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
Click to expand...
 
J

JMB

I tried entering numbers in E5:E7 with SUM(E5:E7) in cell E8. With E8
selected, I inserted a row and put a number in the now empty cell E8. Excel
automatically changed the SUM formula (now in E9) to SUM(E5:E8). Is this
similar to the functionality you're looking for?



brent said:
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new
row is added?

I'm looking for a relatively simple solution which can be applied by people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that have
this problem, as well as some of the ugly but traditional workarounds such as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
Click to expand...
 
A

Aladin Akyurek

Easy to adapt:

=SUM(D1:INDEX(D:D,ROW()-1))

which is better than having INDIRECT or even OFFSET.
 
B

Bob Phillips

well forgive us for not being able to appreciate that which we are not told.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

brent said:
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new
row is added?

I'm looking for a relatively simple solution which can be applied by people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that have
this problem, as well as some of the ugly but traditional workarounds such as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
brent said:
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
Click to expand...
http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
 
J

JMB

telepathy not working today? <g>

Bob Phillips said:
well forgive us for not being able to appreciate that which we are not told.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

brent said:
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new
row is added?

I'm looking for a relatively simple solution which can be applied by people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that have
this problem, as well as some of the ugly but traditional workarounds such as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

OP never came back. Too bad he did see my =sa() for simple

--
Don Guillett
SalesAid Software
[email protected]
Bob Phillips said:
well forgive us for not being able to appreciate that which we are not
told.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

brent said:
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new
row is added?

I'm looking for a relatively simple solution which can be applied by people
who are generally going to be less knowledgable than those on this forum.
(I'm sitting at a client corporation right now fixing spreadsheets that have
this problem, as well as some of the ugly but traditional workarounds
such as
blank lines that create their own sets of issues.)

Cheers.

ps please nobody be offended by the Disagree buttons that I clicked. I
thought that would bring up a comment section -- it took a while to spot the
Reply button.


Don Guillett said:
try this
=SUM(INDIRECT("A4:a"&ROW()-1))

--
Don Guillett
SalesAid Software
[email protected]
Suggested new Excel function: SUMABOVE(Ref)

Ref = Single cell reference in the same column as the function. The
function
automatically adds all values from REF through the cell preceding the
function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates
issues of inserting rows immediately above the formula row and having to
adjust the formula to include them. Can also work similar to SUBTOTAL
function, where overlapping SUMABOVE functions do not duplicate the
amounts
added.

Alternatively, REF can be in a different column and a block will be added,
i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN


----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...
Click to expand...
Click to expand...
 
Top