Excel Sum Product / Date Range

[Thomas Hochard]

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>=DATEVALUE("1/1/2020"))*(B2:B317<=DATEVALUE("6/20/2020"))) 

Is there an adaptation of these formulas that would count the number of occurrences in Column A from 07-01-19 to 06-30-20, where the occurrence is prior to 09-01-19 and received from after 09-01-19 to 06-30-20? 

Thank you.

[#2]

I believe this is the solution:
=SUMPRODUCT(((A2:B317>=DATEVALUE("7/1/2019"))*(B2:B317<=DATEVALUE("6/30/2020")*(A2:B317<=DATEVALUE("9/1/2019")))))

[#3]

@Thomas Hochard  you answer doesn't seem to meet what you requested.  Here is my options:

=COUNTIFS(A2:A317,"<="&DATEVALUE("2019-09-01"),B2:B317,">="&DATEVALUE("2019-09-01"),B2:B317,"<="&DATEVALUE("2020-06-30"))

in the attached I added a table where you can enter start/end dates for each for various counts.  You also can just use the filter (down arrow) on the table to filter by date ranges and then just look at the ribbon on the bottom to see how many records are found. 

[#4]

@Thomas Hochard 

In addition, I'd do not recommend to use in formulas components like DATEVALUE("7/1/2019"). It's always better to keep all parameters, as dates in this case, in separate cells. Even if hardcode within the formula it's better to use DATE(2019,7,1).  DATEVALUE with the text as above could not work in international environment.

[#5]

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.

[#6]

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.