Sum only if not blank
I'm trying to put together a formula that sums 3 cells, but if they're blank I don't want to sum them.
If there's no values in the cell, it gives me "0" as a result, but I only want to have a "0" if the cells value are actually "0" and not blank.
As an example i used this formula:
=IF(COUNTA(J6,J7,J8)<3,"",SUM(J6,J7,J8))
It's not giving me a result until I fill the 3 values, and I want it to sum it even if I only have 2 values.
Any tips?
microsoft-excel
edited Nov 22 at 15:42
PeterH
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
add a comment |
I'm trying to put together a formula that sums 3 cells, but if they're blank I don't want to sum them.
If there's no values in the cell, it gives me "0" as a result, but I only want to have a "0" if the cells value are actually "0" and not blank.
As an example i used this formula:
=IF(COUNTA(J6,J7,J8)<3,"",SUM(J6,J7,J8))
It's not giving me a result until I fill the 3 values, and I want it to sum it even if I only have 2 values.
Any tips?
microsoft-excel
edited Nov 22 at 15:42
PeterH
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
add a comment |
I'm trying to put together a formula that sums 3 cells, but if they're blank I don't want to sum them.
If there's no values in the cell, it gives me "0" as a result, but I only want to have a "0" if the cells value are actually "0" and not blank.
As an example i used this formula:
=IF(COUNTA(J6,J7,J8)<3,"",SUM(J6,J7,J8))
It's not giving me a result until I fill the 3 values, and I want it to sum it even if I only have 2 values.
Any tips?
microsoft-excel
edited Nov 22 at 15:42
PeterH
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
I'm trying to put together a formula that sums 3 cells, but if they're blank I don't want to sum them.
If there's no values in the cell, it gives me "0" as a result, but I only want to have a "0" if the cells value are actually "0" and not blank.
As an example i used this formula:
=IF(COUNTA(J6,J7,J8)<3,"",SUM(J6,J7,J8))
It's not giving me a result until I fill the 3 values, and I want it to sum it even if I only have 2 values.
Any tips?
microsoft-excel
microsoft-excel
edited Nov 22 at 15:42
PeterH
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
edited Nov 22 at 15:42
PeterH
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
edited Nov 22 at 15:42
PeterH
3,44332246
edited Nov 22 at 15:42
PeterH
3,44332246
edited Nov 22 at 15:42
PeterH
3,44332246
3,44332246
asked Nov 22 at 15:40
Luis Bernal
1
asked Nov 22 at 15:40
Luis Bernal
1
asked Nov 22 at 15:40
Luis Bernal
1
1
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)
Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
add a comment |
Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))
answered Nov 23 at 3:22
Lee
87427
add a comment |
I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}
Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for
all Blank Cells(Situation 5).
IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
answered Nov 23 at 8:06
Rajesh S
3,7021522
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%2f1377608%2fsum-only-if-not-blank%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)
Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
add a comment |
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)
Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
add a comment |
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)
Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)
Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
answered Nov 22 at 15:47
cybernetic.nomad
1,222112
1,222112
add a comment |
add a comment |
Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))
answered Nov 23 at 3:22
Lee
87427
add a comment |
Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))
answered Nov 23 at 3:22
Lee
87427
add a comment |
Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))
answered Nov 23 at 3:22
Lee
87427
Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))
answered Nov 23 at 3:22
Lee
87427
answered Nov 23 at 3:22
Lee
87427
answered Nov 23 at 3:22
Lee
87427
answered Nov 23 at 3:22
Lee
87427
87427
add a comment |
add a comment |
I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}
Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for
all Blank Cells(Situation 5).
IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
answered Nov 23 at 8:06
Rajesh S
3,7021522
add a comment |
I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}
Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for
all Blank Cells(Situation 5).
IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
answered Nov 23 at 8:06
Rajesh S
3,7021522
add a comment |
I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}
Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for
all Blank Cells(Situation 5).
IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
answered Nov 23 at 8:06
Rajesh S
3,7021522
I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}
Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for
all Blank Cells(Situation 5).
IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
answered Nov 23 at 8:06
Rajesh S
3,7021522
answered Nov 23 at 8:06
Rajesh S
3,7021522
answered Nov 23 at 8:06
Rajesh S
3,7021522
answered Nov 23 at 8:06
Rajesh S
3,7021522
3,7021522
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1377608%2fsum-only-if-not-blank%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
