New Contributor

# 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>=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.

3 Replies

# Re: Excel Sum Product / Date Range

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

# Re: Excel Sum Product / Date Range

@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.

# Re: Excel Sum Product / Date Range

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.