Excel: Grading Formula to Omit Certain Cells
I have an excel sheet I've made for a class.
I have pre-filled in all of the assignments:
| A | B | C
|-------------------------------------------
1|Assignments |Points Received |Total Points
2|Assignment 1 |10 |10
3|Assignment 2 |? |10
4|Quiz 1 |9 |10
5|Assignment 3 |? |10
6|... | |
I'm trying to figure out how I can sum up the points under total for only those cells that have an actual number in the cell directly left to it. Or, get the sum of the total points if the left cell isn't "?". How can I do this? I do not want to remove the points in Total Points column for which the is an associated "?" symbol in the Points Received column.
Essentially, I want the grade to display the current percent of only completed assignments (in the example it would be 19/20, not 19/40)
microsoft-excel
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
add a comment |
I have an excel sheet I've made for a class.
I have pre-filled in all of the assignments:
| A | B | C
|-------------------------------------------
1|Assignments |Points Received |Total Points
2|Assignment 1 |10 |10
3|Assignment 2 |? |10
4|Quiz 1 |9 |10
5|Assignment 3 |? |10
6|... | |
I'm trying to figure out how I can sum up the points under total for only those cells that have an actual number in the cell directly left to it. Or, get the sum of the total points if the left cell isn't "?". How can I do this? I do not want to remove the points in Total Points column for which the is an associated "?" symbol in the Points Received column.
Essentially, I want the grade to display the current percent of only completed assignments (in the example it would be 19/20, not 19/40)
microsoft-excel
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
add a comment |
I have an excel sheet I've made for a class.
I have pre-filled in all of the assignments:
| A | B | C
|-------------------------------------------
1|Assignments |Points Received |Total Points
2|Assignment 1 |10 |10
3|Assignment 2 |? |10
4|Quiz 1 |9 |10
5|Assignment 3 |? |10
6|... | |
I'm trying to figure out how I can sum up the points under total for only those cells that have an actual number in the cell directly left to it. Or, get the sum of the total points if the left cell isn't "?". How can I do this? I do not want to remove the points in Total Points column for which the is an associated "?" symbol in the Points Received column.
Essentially, I want the grade to display the current percent of only completed assignments (in the example it would be 19/20, not 19/40)
microsoft-excel
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
I have an excel sheet I've made for a class.
I have pre-filled in all of the assignments:
| A | B | C
|-------------------------------------------
1|Assignments |Points Received |Total Points
2|Assignment 1 |10 |10
3|Assignment 2 |? |10
4|Quiz 1 |9 |10
5|Assignment 3 |? |10
6|... | |
I'm trying to figure out how I can sum up the points under total for only those cells that have an actual number in the cell directly left to it. Or, get the sum of the total points if the left cell isn't "?". How can I do this? I do not want to remove the points in Total Points column for which the is an associated "?" symbol in the Points Received column.
Essentially, I want the grade to display the current percent of only completed assignments (in the example it would be 19/20, not 19/40)
microsoft-excel
microsoft-excel
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
edited Jan 17 at 6:12
joe_04_04
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
asked Jan 17 at 5:46
joe_04_04joe_04_04
1105
1105
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Assuming the top left cell (Assignments) in your example is A1...
For Points Received, use the regular SUM() function. It will treat the non-numeric cells as zeros:
=SUM(B2:B5)
For Total Points, use the SUMIF() function:
=SUMIF(B2:B5,">0",C2:C5)
This tells Excel to look at the cells in B2:B5 (Points Received) and IF they meet the criteria (>0) then take the SUM of corresponding cells in C2:C5 (Total Points).
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
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%2f1395224%2fexcel-grading-formula-to-omit-certain-cells%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
Assuming the top left cell (Assignments) in your example is A1...
For Points Received, use the regular SUM() function. It will treat the non-numeric cells as zeros:
=SUM(B2:B5)
For Total Points, use the SUMIF() function:
=SUMIF(B2:B5,">0",C2:C5)
This tells Excel to look at the cells in B2:B5 (Points Received) and IF they meet the criteria (>0) then take the SUM of corresponding cells in C2:C5 (Total Points).
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
add a comment |
Assuming the top left cell (Assignments) in your example is A1...
For Points Received, use the regular SUM() function. It will treat the non-numeric cells as zeros:
=SUM(B2:B5)
For Total Points, use the SUMIF() function:
=SUMIF(B2:B5,">0",C2:C5)
This tells Excel to look at the cells in B2:B5 (Points Received) and IF they meet the criteria (>0) then take the SUM of corresponding cells in C2:C5 (Total Points).
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
add a comment |
Assuming the top left cell (Assignments) in your example is A1...
For Points Received, use the regular SUM() function. It will treat the non-numeric cells as zeros:
=SUM(B2:B5)
For Total Points, use the SUMIF() function:
=SUMIF(B2:B5,">0",C2:C5)
This tells Excel to look at the cells in B2:B5 (Points Received) and IF they meet the criteria (>0) then take the SUM of corresponding cells in C2:C5 (Total Points).
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
Assuming the top left cell (Assignments) in your example is A1...
For Points Received, use the regular SUM() function. It will treat the non-numeric cells as zeros:
=SUM(B2:B5)
For Total Points, use the SUMIF() function:
=SUMIF(B2:B5,">0",C2:C5)
This tells Excel to look at the cells in B2:B5 (Points Received) and IF they meet the criteria (>0) then take the SUM of corresponding cells in C2:C5 (Total Points).
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Jan 17 at 6:09
Mike FitzpatrickMike Fitzpatrick
14.5k33540
14.5k33540
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
add a comment |
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
I did update my submission to include the column labels. I will try your solution now.
– joe_04_04
Jan 17 at 6:11
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
Thank you very much! This worked!
– joe_04_04
Jan 17 at 6:17
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%2f1395224%2fexcel-grading-formula-to-omit-certain-cells%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
