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>=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.
5 Replies
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.
- 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")))))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.
- 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.
