Excel - SumIF Cell Value is between date range & another Cell is FalseExcel Formula: Count cells where value is dateExcel, SUMIFS criteria range is also sum rangeSUMIFS with date and name criteria… month and year onlySUMIFS based on name and date rangeSumifs unique values in range only with multiple criteriaVALUE() For a rangeGetting VALUE error in Excel using a sum range and criteria 1 with a single cell criteria 2Excel VBA to SUMIF Range only if Range is not completely BlankSUMIF formula does not work if the criteria range cells contain formulas instead of just valuesSumIF Using Table/Named Range Instead of Single Cell Criteria
Locked-up DOS computer beeped on keypress. What mechanism caused that?
Wildcards not interpreted by shell script when used with output redirection
How to tell if JDK is available from within running JVM?
Why don't humans perceive waves as twice the frequency they are?
How much solution to fill Paterson Universal Tank when developing film?
How slow ( not zero) can a car engine run without hurting engine and saving on fuel
Who or what determines if a curse is valid or not?
Do higher dimensions have axes?
Does 5e follow the Primary Source rule?
Applying for jobs with an obvious scar
How to extract interesting piece of output in bash
Operation Unzalgo
How did Jayne know when to shoot?
How do you send money when you're not sure it's not a scam?
Is encryption still applied if you ignore the SSL certificate warning for self-signed certs?
Tuning G3 string to A3 guitar
Improving an O(N^2) function (all entities iterating over all other entities)
What makes MOVEQ quicker than a normal MOVE in 68000 assembly?
How did J. J. Thomson establish the particle nature of the electron?
Manager asking me to eat breakfast from now on
How can I help our ranger feel special about her beast companion?
Is it possible to invoke "super" with less ambiguous results?
Last-minute canceled work-trip mean I'll lose thousands of dollars on planned vacation
What is a Romeo Word™?
Excel - SumIF Cell Value is between date range & another Cell is False
Excel Formula: Count cells where value is dateExcel, SUMIFS criteria range is also sum rangeSUMIFS with date and name criteria… month and year onlySUMIFS based on name and date rangeSumifs unique values in range only with multiple criteriaVALUE() For a rangeGetting VALUE error in Excel using a sum range and criteria 1 with a single cell criteria 2Excel VBA to SUMIF Range only if Range is not completely BlankSUMIF formula does not work if the criteria range cells contain formulas instead of just valuesSumIF Using Table/Named Range Instead of Single Cell Criteria
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Need help writing a formula to do the following
Sum the value of cells that fall withing a specified date range BUT
only if the corresponding cell range A3:A21 is False
I have the current formula that handles just the date range comparison.
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9)
- Cells A9 on Essential Info handle the date for the start of the week,
- Cells B9 on Essential Info handle the date for the end of the week,
- H3:H21 Contains the number values to be added,
- I3:I21 Contains the Date Values to be compared to A9 & B9 on Essential info.
- A3:A21 Contains a true or false value based on a tick box.
I am falling over at the "If Cell Range is false" part.
Any pointers would be greatly appreciated
excel excel-formula
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
add a comment |
Need help writing a formula to do the following
Sum the value of cells that fall withing a specified date range BUT
only if the corresponding cell range A3:A21 is False
I have the current formula that handles just the date range comparison.
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9)
- Cells A9 on Essential Info handle the date for the start of the week,
- Cells B9 on Essential Info handle the date for the end of the week,
- H3:H21 Contains the number values to be added,
- I3:I21 Contains the Date Values to be compared to A9 & B9 on Essential info.
- A3:A21 Contains a true or false value based on a tick box.
I am falling over at the "If Cell Range is false" part.
Any pointers would be greatly appreciated
excel excel-formula
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
add a comment |
Need help writing a formula to do the following
Sum the value of cells that fall withing a specified date range BUT
only if the corresponding cell range A3:A21 is False
I have the current formula that handles just the date range comparison.
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9)
- Cells A9 on Essential Info handle the date for the start of the week,
- Cells B9 on Essential Info handle the date for the end of the week,
- H3:H21 Contains the number values to be added,
- I3:I21 Contains the Date Values to be compared to A9 & B9 on Essential info.
- A3:A21 Contains a true or false value based on a tick box.
I am falling over at the "If Cell Range is false" part.
Any pointers would be greatly appreciated
excel excel-formula
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
Need help writing a formula to do the following
Sum the value of cells that fall withing a specified date range BUT
only if the corresponding cell range A3:A21 is False
I have the current formula that handles just the date range comparison.
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9)
- Cells A9 on Essential Info handle the date for the start of the week,
- Cells B9 on Essential Info handle the date for the end of the week,
- H3:H21 Contains the number values to be added,
- I3:I21 Contains the Date Values to be compared to A9 & B9 on Essential info.
- A3:A21 Contains a true or false value based on a tick box.
I am falling over at the "If Cell Range is false" part.
Any pointers would be greatly appreciated
excel excel-formula
excel excel-formula
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
asked Mar 26 at 10:52
BatteredburritoBatteredburrito
3601 silver badge18 bronze badges
3601 silver badge18 bronze badges
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Try to append your formula with this , A3:A21, "false").
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
add a comment |
Use Sum product Instead of sum ifs:
=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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%2fstackoverflow.com%2fquestions%2f55355344%2fexcel-sumif-cell-value-is-between-date-range-another-cell-is-false%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
Try to append your formula with this , A3:A21, "false").
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
add a comment |
Try to append your formula with this , A3:A21, "false").
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
add a comment |
Try to append your formula with this , A3:A21, "false").
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
Try to append your formula with this , A3:A21, "false").
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
answered Mar 26 at 11:02
Imtiaz AhmedImtiaz Ahmed
2931 silver badge10 bronze badges
2931 silver badge10 bronze badges
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
add a comment |
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9, B3:B21,FALSE)
– Batteredburrito
Mar 26 at 11:04
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Welcome, is it working properly? And it is always helpful if while posting questions,you can share the pic of the some of the data
– Imtiaz Ahmed
Mar 26 at 11:07
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
Working perfectly thank you. Unfortunately the data was a bit hard to censor out the sensitive parts in this scenario. I will try to provide more information in future. Thank you for the help
– Batteredburrito
Mar 26 at 11:10
add a comment |
Use Sum product Instead of sum ifs:
=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
add a comment |
Use Sum product Instead of sum ifs:
=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
add a comment |
Use Sum product Instead of sum ifs:
=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
Use Sum product Instead of sum ifs:
=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
edited Mar 26 at 12:57
Wai Ha Lee
6,37612 gold badges43 silver badges66 bronze badges
6,37612 gold badges43 silver badges66 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
answered Mar 26 at 11:10
Manoj BabuManoj Babu
312 bronze badges
312 bronze badges
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
add a comment |
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
This is fine for normal sum ranges but excludes the "If A3:A21 is false, do not add" issue. I appreciate the input though. Above solution works perfectly for my purpose
– Batteredburrito
Mar 26 at 11:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f55355344%2fexcel-sumif-cell-value-is-between-date-range-another-cell-is-false%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
