Styjun

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;

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

share|improve this question

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

add a comment | 

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

share|improve this question

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 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

share|improve this question

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

=SUMIFS(H3:H21,I3:I21,">="&'Essential Info'!A9,I3:I21,"<="&'Essential Info'!B9)

share|improve this question

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

share|improve this question

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

asked Mar 26 at 10:52

BatteredburritoBatteredburrito

36011 silver badge1818 bronze badges

2 Answers
2

active

oldest

votes

1

Try to append your formula with this , A3:A21, "false").

share|improve this answer

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 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 | 

0

Use Sum product Instead of sum ifs:

=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)

share|improve this answer

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 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
);



);

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

1

Try to append your formula with this , A3:A21, "false").

share|improve this answer

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 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 | 

1

Try to append your formula with this , A3:A21, "false").

share|improve this answer

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 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").

share|improve this answer

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 bronze badges

share|improve this answer

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 bronze badges

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 bronze badges

answered Mar 26 at 11:02

Imtiaz AhmedImtiaz Ahmed

29311 silver badge1010 bronze badges

0

Use Sum product Instead of sum ifs:

=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)

share|improve this answer

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 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 | 

0

Use Sum product Instead of sum ifs:

=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)

share|improve this answer

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 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)

share|improve this answer

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 bronze badges

=Sumproduct((I3:I21>='Essential Info'!A9)*(I3:I21>='Essential Info'!B9)*H3:H21)

share|improve this answer

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 bronze badges

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

edited Mar 26 at 12:57

Wai Ha Lee

6,3761212 gold badges4343 silver badges6666 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 bronze badges

answered Mar 26 at 11:10

Manoj BabuManoj Babu

3122 bronze badges