Excel summing named range
I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to amend the formula, is a way where I can define the range of cells just once and use that reference in my individual worksheets? Something like myrange =C2:c50 and then on each sheet have the formula =sum(myrange). How do I do that, please?
microsoft-excel worksheet-function range
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
add a comment |
I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to amend the formula, is a way where I can define the range of cells just once and use that reference in my individual worksheets? Something like myrange =C2:c50 and then on each sheet have the formula =sum(myrange). How do I do that, please?
microsoft-excel worksheet-function range
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
add a comment |
I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to amend the formula, is a way where I can define the range of cells just once and use that reference in my individual worksheets? Something like myrange =C2:c50 and then on each sheet have the formula =sum(myrange). How do I do that, please?
microsoft-excel worksheet-function range
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to amend the formula, is a way where I can define the range of cells just once and use that reference in my individual worksheets? Something like myrange =C2:c50 and then on each sheet have the formula =sum(myrange). How do I do that, please?
microsoft-excel worksheet-function range
microsoft-excel worksheet-function range
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
edited Oct 11 '13 at 15:37
John Bensin
1,2651321
1,2651321
asked Oct 11 '13 at 13:40
silasila
313
asked Oct 11 '13 at 13:40
silasila
313
asked Oct 11 '13 at 13:40
silasila
313
313
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:
Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):
Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.
When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.
Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
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%2f657524%2fexcel-summing-named-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:
Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):
Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.
When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.
Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
add a comment |
You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:
Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):
Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.
When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.
Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
add a comment |
You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:
Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):
Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.
When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.
Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:
Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):
Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.
When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.
Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
1
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
answered Oct 11 '13 at 13:41
John BensinJohn Bensin
1,2651321
1,2651321
add a comment |
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%2f657524%2fexcel-summing-named-range%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
