Forum Discussion
Excel Sum Product / Date Range
I'm using the formula below to count the number of occurrences between the range of dates. =SUMPRODUCT((A2:A317>=DATEVALUE("7/1/2019"))*(A2:A317<=DATEVALUE("6/20/2020"))) =SUMPRODUCT((B2:B317>=DAT...
Is there also an alternative to featuring hardcoded dates in this formula? I'm trying to source from a date table to avoid keying in manually.
as noted by Sergei it is not only possible but preferred. Instead of DATEVALUE(xxx) or DATE(xxx) just put the cell reference where that date can be found. Or you can even do a lookup in a table for which date to use. so the above formula could use Z1 and Z2 as the dates like so:
=COUNTIFS(A2:A317,"<=" & $Z$1, B2:B317,">=" & $Z$1, B2:B317,"<=" & $Z$2)
If you need additional help I suggest maybe opening a new thread and give more specifics of what you are trying to do or what isn't working.
=COUNTIFS(A2:A317,"<=" & $Z$1, B2:B317,">=" & $Z$1, B2:B317,"<=" & $Z$2)
If you need additional help I suggest maybe opening a new thread and give more specifics of what you are trying to do or what isn't working.