In excel how can I sum all the cells above the current cell upto a particular cell
What formula should I apply to add all the cells above the current cell but only up to a cell with a particular text.
Like in case of the below example the formula adds all cells above it but only up to "Amount", not above that, so that if cells are inserted in between they get added too.
Amount
1000
500
345
560
Formula
microsoft-excel worksheet-function
asked Jan 19 at 18:49
AniketAniket
41
add a comment |
What formula should I apply to add all the cells above the current cell but only up to a cell with a particular text.
Like in case of the below example the formula adds all cells above it but only up to "Amount", not above that, so that if cells are inserted in between they get added too.
Amount
1000
500
345
560
Formula
microsoft-excel worksheet-function
asked Jan 19 at 18:49
AniketAniket
41
Are you giving us the full scenario? If you can see the cell withAmount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.
– teylyn
Jan 19 at 19:50
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44
add a comment |
What formula should I apply to add all the cells above the current cell but only up to a cell with a particular text.
Like in case of the below example the formula adds all cells above it but only up to "Amount", not above that, so that if cells are inserted in between they get added too.
Amount
1000
500
345
560
Formula
microsoft-excel worksheet-function
asked Jan 19 at 18:49
AniketAniket
41
What formula should I apply to add all the cells above the current cell but only up to a cell with a particular text.
Like in case of the below example the formula adds all cells above it but only up to "Amount", not above that, so that if cells are inserted in between they get added too.
Amount
1000
500
345
560
Formula
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 19 at 18:49
AniketAniket
41
asked Jan 19 at 18:49
AniketAniket
41
asked Jan 19 at 18:49
AniketAniket
41
asked Jan 19 at 18:49
AniketAniket
41
asked Jan 19 at 18:49
AniketAniket
41
41
Are you giving us the full scenario? If you can see the cell withAmount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.
– teylyn
Jan 19 at 19:50
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44
add a comment |
Are you giving us the full scenario? If you can see the cell withAmount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.
– teylyn
Jan 19 at 19:50
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44
Are you giving us the full scenario? If you can see the cell with
Amount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.– teylyn
Jan 19 at 19:50
Are you giving us the full scenario? If you can see the cell with
Amount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.– teylyn
Jan 19 at 19:50
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44
add a comment |
2 Answers
2
active
oldest
votes
If the cell with the text Amount is in A1, you can use
=sum($A$2:a5)
If a row is inserted between rows 2 and 5, then the formula will update to
=sum($A$2:a6)
If, however, you add a new row above row 6, the formula will not change. In that case you man want to have a blank row above the formula. Include the blank row in the sum formula. When you want to insert a new row, select the blank row and insert the row, then the formula will update.
Or, you can save all that hassle and use an Excel Table object (Insert ribbon > Table) turn on table totals, then you don't have to write a formula at all. To enter a new row at the bottom of the table, select the last cell in the last row and hit the Tab key.
You can also create the formula manually if you need it outside the table, like this.
=sum(Table1[amount])
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
add a comment |
This is a little clunky, so there is probably a way to optimize, but try this.
=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1396144%2fin-excel-how-can-i-sum-all-the-cells-above-the-current-cell-upto-a-particular-ce%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If the cell with the text Amount is in A1, you can use
=sum($A$2:a5)
If a row is inserted between rows 2 and 5, then the formula will update to
=sum($A$2:a6)
If, however, you add a new row above row 6, the formula will not change. In that case you man want to have a blank row above the formula. Include the blank row in the sum formula. When you want to insert a new row, select the blank row and insert the row, then the formula will update.
Or, you can save all that hassle and use an Excel Table object (Insert ribbon > Table) turn on table totals, then you don't have to write a formula at all. To enter a new row at the bottom of the table, select the last cell in the last row and hit the Tab key.
You can also create the formula manually if you need it outside the table, like this.
=sum(Table1[amount])
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
add a comment |
If the cell with the text Amount is in A1, you can use
=sum($A$2:a5)
If a row is inserted between rows 2 and 5, then the formula will update to
=sum($A$2:a6)
If, however, you add a new row above row 6, the formula will not change. In that case you man want to have a blank row above the formula. Include the blank row in the sum formula. When you want to insert a new row, select the blank row and insert the row, then the formula will update.
Or, you can save all that hassle and use an Excel Table object (Insert ribbon > Table) turn on table totals, then you don't have to write a formula at all. To enter a new row at the bottom of the table, select the last cell in the last row and hit the Tab key.
You can also create the formula manually if you need it outside the table, like this.
=sum(Table1[amount])
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
add a comment |
If the cell with the text Amount is in A1, you can use
=sum($A$2:a5)
If a row is inserted between rows 2 and 5, then the formula will update to
=sum($A$2:a6)
If, however, you add a new row above row 6, the formula will not change. In that case you man want to have a blank row above the formula. Include the blank row in the sum formula. When you want to insert a new row, select the blank row and insert the row, then the formula will update.
Or, you can save all that hassle and use an Excel Table object (Insert ribbon > Table) turn on table totals, then you don't have to write a formula at all. To enter a new row at the bottom of the table, select the last cell in the last row and hit the Tab key.
You can also create the formula manually if you need it outside the table, like this.
=sum(Table1[amount])
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
If the cell with the text Amount is in A1, you can use
=sum($A$2:a5)
If a row is inserted between rows 2 and 5, then the formula will update to
=sum($A$2:a6)
If, however, you add a new row above row 6, the formula will not change. In that case you man want to have a blank row above the formula. Include the blank row in the sum formula. When you want to insert a new row, select the blank row and insert the row, then the formula will update.
Or, you can save all that hassle and use an Excel Table object (Insert ribbon > Table) turn on table totals, then you don't have to write a formula at all. To enter a new row at the bottom of the table, select the last cell in the last row and hit the Tab key.
You can also create the formula manually if you need it outside the table, like this.
=sum(Table1[amount])
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
answered Jan 19 at 19:56
teylynteylyn
17.3k22539
17.3k22539
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
add a comment |
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
+1 for table, the simple solution.
– fixer1234
Jan 19 at 23:02
add a comment |
This is a little clunky, so there is probably a way to optimize, but try this.
=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
add a comment |
This is a little clunky, so there is probably a way to optimize, but try this.
=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
add a comment |
This is a little clunky, so there is probably a way to optimize, but try this.
=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
This is a little clunky, so there is probably a way to optimize, but try this.
=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
answered Jan 19 at 22:20
BruceWayneBruceWayne
1,9011721
1,9011721
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
add a comment |
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
I was going to post almost the identical answer until I realized that even that isn't bulletproof. There could be multiple ranges, with "Amount" appearing more than once. The formula could get insane coping with every possibility. Figured it made sense to just ask the OP what requirements they needed to handle. :-)
– fixer1234
Jan 19 at 22:59
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
Please, not Indirect. This can be done non-volatile,
– teylyn
Jan 20 at 1:42
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1396144%2fin-excel-how-can-i-sum-all-the-cells-above-the-current-cell-upto-a-particular-ce%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you giving us the full scenario? If you can see the cell with
Amount, just start the sum one cell below. If it is more complex than that, please edit your question and provide the missing details.– teylyn
Jan 19 at 19:50
The starting point is trivial, just specify it. Adding rows will be accommodated as long as they are within your range. If you add above or below the range endpoints, you will need provisions to handle that. Can you clarify the requirements better?
– fixer1234
Jan 19 at 21:44