Excel - SumIF Cell Value is between date range & another Cell is False

0
votes

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

2
excelexcel-formula

2 Answers

1
votes

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

0
votes

Use Sum product Instead of sum ifs:

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