Sum results of drop down list in Excel
I am using drop-down lists for the observation, self-practice, and teaching tabs, and I would like to total the amount of time in column J. Is there a formula that will allow me to do this?
microsoft-excel
edited Feb 15 at 4:35
Ted D.
75028
asked Feb 15 at 2:08
holliholli
1
add a comment |
I am using drop-down lists for the observation, self-practice, and teaching tabs, and I would like to total the amount of time in column J. Is there a formula that will allow me to do this?
microsoft-excel
edited Feb 15 at 4:35
Ted D.
75028
asked Feb 15 at 2:08
holliholli
1
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like0 " minutes". Then you could have numbers that could be summed without any special formulas.
– Rey Juna
Feb 15 at 17:50
add a comment |
I am using drop-down lists for the observation, self-practice, and teaching tabs, and I would like to total the amount of time in column J. Is there a formula that will allow me to do this?
microsoft-excel
edited Feb 15 at 4:35
Ted D.
75028
asked Feb 15 at 2:08
holliholli
1
I am using drop-down lists for the observation, self-practice, and teaching tabs, and I would like to total the amount of time in column J. Is there a formula that will allow me to do this?
microsoft-excel
microsoft-excel
edited Feb 15 at 4:35
Ted D.
75028
asked Feb 15 at 2:08
holliholli
1
edited Feb 15 at 4:35
Ted D.
75028
asked Feb 15 at 2:08
holliholli
1
edited Feb 15 at 4:35
Ted D.
75028
edited Feb 15 at 4:35
Ted D.
75028
edited Feb 15 at 4:35
Ted D.
75028
75028
asked Feb 15 at 2:08
holliholli
1
asked Feb 15 at 2:08
holliholli
1
asked Feb 15 at 2:08
holliholli
1
1
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like0 " minutes". Then you could have numbers that could be summed without any special formulas.
– Rey Juna
Feb 15 at 17:50
add a comment |
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like0 " minutes". Then you could have numbers that could be summed without any special formulas.
– Rey Juna
Feb 15 at 17:50
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like
0 " minutes". Then you could have numbers that could be summed without any special formulas.– Rey Juna
Feb 15 at 17:50
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like
0 " minutes". Then you could have numbers that could be summed without any special formulas.– Rey Juna
Feb 15 at 17:50
add a comment |
1 Answer
1
active
oldest
votes
The solution is very simple if the cells only contain numbers and no other characters. If this were the case the following formula would go in cell J2 and could be copied drug down automatically updating all the row references for the current row total:
=SUM(G2:I2)
This is the typical way Excel is used, one idea per cell. The labels and units are specified elsewhere, such as column headings.
However, in the stated problem, a formula is needed to isolate the number in the text. To reduce the complexity, we'll assume the number is before any other characters (leading spaces are ok). We will further assume there is at least one space between the number and any following character(s).
This solution allows for empty cells, leading spaces, multiple spaces and a number by itself (or with just spaces before and/or after the number).
=IFERROR( NUMBERVALUE( LEFT( TRIM(F2) & " ", FIND( " ", TRIM(F2) & " " ) - 1)), 0) + IFERROR( NUMBERVALUE( LEFT( TRIM(G2) & " ", FIND( " ", TRIM(G2) & " ") - 1)) ,0) + IFERROR( NUMBERVALUE( LEFT( TRIM(H2) & " ", FIND( " ", TRIM(H2) & " ") -1 )), 0)
Very convoluted, right?!
Here is a slightly more compact version but it is a special formula called an array formula. After pasting it into the cell, perform a special Enter key combination to make it an array formula. Otherwise Excel may say something is wrong with the formula or the results may be incorrect.
=SUM( IFERROR( NUMBERVALUE( LEFT( TRIM(F2:H2) & " ", FIND( " ", TRIM(F2:H2) & " " ) - 1)), 0))
Once the formula is pasted into the cell, make it an array formula by pressing Ctrl-Shift-Enter. This is sometimes referred to by the acronym for this key press combination CSE (Control Shift Enter).
Everytime an array formula is edited, press CSE.
answered Feb 15 at 3:24
Ted D.Ted D.
75028
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%2f1405921%2fsum-results-of-drop-down-list-in-excel%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
The solution is very simple if the cells only contain numbers and no other characters. If this were the case the following formula would go in cell J2 and could be copied drug down automatically updating all the row references for the current row total:
=SUM(G2:I2)
This is the typical way Excel is used, one idea per cell. The labels and units are specified elsewhere, such as column headings.
However, in the stated problem, a formula is needed to isolate the number in the text. To reduce the complexity, we'll assume the number is before any other characters (leading spaces are ok). We will further assume there is at least one space between the number and any following character(s).
This solution allows for empty cells, leading spaces, multiple spaces and a number by itself (or with just spaces before and/or after the number).
=IFERROR( NUMBERVALUE( LEFT( TRIM(F2) & " ", FIND( " ", TRIM(F2) & " " ) - 1)), 0) + IFERROR( NUMBERVALUE( LEFT( TRIM(G2) & " ", FIND( " ", TRIM(G2) & " ") - 1)) ,0) + IFERROR( NUMBERVALUE( LEFT( TRIM(H2) & " ", FIND( " ", TRIM(H2) & " ") -1 )), 0)
Very convoluted, right?!
Here is a slightly more compact version but it is a special formula called an array formula. After pasting it into the cell, perform a special Enter key combination to make it an array formula. Otherwise Excel may say something is wrong with the formula or the results may be incorrect.
=SUM( IFERROR( NUMBERVALUE( LEFT( TRIM(F2:H2) & " ", FIND( " ", TRIM(F2:H2) & " " ) - 1)), 0))
Once the formula is pasted into the cell, make it an array formula by pressing Ctrl-Shift-Enter. This is sometimes referred to by the acronym for this key press combination CSE (Control Shift Enter).
Everytime an array formula is edited, press CSE.
answered Feb 15 at 3:24
Ted D.Ted D.
75028
add a comment |
The solution is very simple if the cells only contain numbers and no other characters. If this were the case the following formula would go in cell J2 and could be copied drug down automatically updating all the row references for the current row total:
=SUM(G2:I2)
This is the typical way Excel is used, one idea per cell. The labels and units are specified elsewhere, such as column headings.
However, in the stated problem, a formula is needed to isolate the number in the text. To reduce the complexity, we'll assume the number is before any other characters (leading spaces are ok). We will further assume there is at least one space between the number and any following character(s).
This solution allows for empty cells, leading spaces, multiple spaces and a number by itself (or with just spaces before and/or after the number).
=IFERROR( NUMBERVALUE( LEFT( TRIM(F2) & " ", FIND( " ", TRIM(F2) & " " ) - 1)), 0) + IFERROR( NUMBERVALUE( LEFT( TRIM(G2) & " ", FIND( " ", TRIM(G2) & " ") - 1)) ,0) + IFERROR( NUMBERVALUE( LEFT( TRIM(H2) & " ", FIND( " ", TRIM(H2) & " ") -1 )), 0)
Very convoluted, right?!
Here is a slightly more compact version but it is a special formula called an array formula. After pasting it into the cell, perform a special Enter key combination to make it an array formula. Otherwise Excel may say something is wrong with the formula or the results may be incorrect.
=SUM( IFERROR( NUMBERVALUE( LEFT( TRIM(F2:H2) & " ", FIND( " ", TRIM(F2:H2) & " " ) - 1)), 0))
Once the formula is pasted into the cell, make it an array formula by pressing Ctrl-Shift-Enter. This is sometimes referred to by the acronym for this key press combination CSE (Control Shift Enter).
Everytime an array formula is edited, press CSE.
answered Feb 15 at 3:24
Ted D.Ted D.
75028
add a comment |
The solution is very simple if the cells only contain numbers and no other characters. If this were the case the following formula would go in cell J2 and could be copied drug down automatically updating all the row references for the current row total:
=SUM(G2:I2)
This is the typical way Excel is used, one idea per cell. The labels and units are specified elsewhere, such as column headings.
However, in the stated problem, a formula is needed to isolate the number in the text. To reduce the complexity, we'll assume the number is before any other characters (leading spaces are ok). We will further assume there is at least one space between the number and any following character(s).
This solution allows for empty cells, leading spaces, multiple spaces and a number by itself (or with just spaces before and/or after the number).
=IFERROR( NUMBERVALUE( LEFT( TRIM(F2) & " ", FIND( " ", TRIM(F2) & " " ) - 1)), 0) + IFERROR( NUMBERVALUE( LEFT( TRIM(G2) & " ", FIND( " ", TRIM(G2) & " ") - 1)) ,0) + IFERROR( NUMBERVALUE( LEFT( TRIM(H2) & " ", FIND( " ", TRIM(H2) & " ") -1 )), 0)
Very convoluted, right?!
Here is a slightly more compact version but it is a special formula called an array formula. After pasting it into the cell, perform a special Enter key combination to make it an array formula. Otherwise Excel may say something is wrong with the formula or the results may be incorrect.
=SUM( IFERROR( NUMBERVALUE( LEFT( TRIM(F2:H2) & " ", FIND( " ", TRIM(F2:H2) & " " ) - 1)), 0))
Once the formula is pasted into the cell, make it an array formula by pressing Ctrl-Shift-Enter. This is sometimes referred to by the acronym for this key press combination CSE (Control Shift Enter).
Everytime an array formula is edited, press CSE.
answered Feb 15 at 3:24
Ted D.Ted D.
75028
The solution is very simple if the cells only contain numbers and no other characters. If this were the case the following formula would go in cell J2 and could be copied drug down automatically updating all the row references for the current row total:
=SUM(G2:I2)
This is the typical way Excel is used, one idea per cell. The labels and units are specified elsewhere, such as column headings.
However, in the stated problem, a formula is needed to isolate the number in the text. To reduce the complexity, we'll assume the number is before any other characters (leading spaces are ok). We will further assume there is at least one space between the number and any following character(s).
This solution allows for empty cells, leading spaces, multiple spaces and a number by itself (or with just spaces before and/or after the number).
=IFERROR( NUMBERVALUE( LEFT( TRIM(F2) & " ", FIND( " ", TRIM(F2) & " " ) - 1)), 0) + IFERROR( NUMBERVALUE( LEFT( TRIM(G2) & " ", FIND( " ", TRIM(G2) & " ") - 1)) ,0) + IFERROR( NUMBERVALUE( LEFT( TRIM(H2) & " ", FIND( " ", TRIM(H2) & " ") -1 )), 0)
Very convoluted, right?!
Here is a slightly more compact version but it is a special formula called an array formula. After pasting it into the cell, perform a special Enter key combination to make it an array formula. Otherwise Excel may say something is wrong with the formula or the results may be incorrect.
=SUM( IFERROR( NUMBERVALUE( LEFT( TRIM(F2:H2) & " ", FIND( " ", TRIM(F2:H2) & " " ) - 1)), 0))
Once the formula is pasted into the cell, make it an array formula by pressing Ctrl-Shift-Enter. This is sometimes referred to by the acronym for this key press combination CSE (Control Shift Enter).
Everytime an array formula is edited, press CSE.
answered Feb 15 at 3:24
Ted D.Ted D.
75028
edited Feb 15 at 6:41
answered Feb 15 at 3:24
Ted D.Ted D.
75028
answered Feb 15 at 3:24
Ted D.Ted D.
75028
answered Feb 15 at 3:24
Ted D.Ted D.
75028
75028
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%2f1405921%2fsum-results-of-drop-down-list-in-excel%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
Does the "minutes" have to be in the drop down list? If it is always minutes, and you just want it to display the word, then you could apply number format like
0 " minutes". Then you could have numbers that could be summed without any special formulas.– Rey Juna
Feb 15 at 17:50