Excel Sum Product / Date Range

%3CLINGO-SUB%20id%3D%22lingo-sub-1558871%22%20slang%3D%22en-US%22%3EExcel%20Sum%20Product%20%2F%20Date%20Range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558871%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20the%20formula%20below%20to%20count%20the%20number%20of%20occurrences%20between%20the%20range%20of%20dates.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((A2%3AA317%26gt%3B%3DDATEVALUE(%227%2F1%2F2019%22))*(A2%3AA317%26lt%3B%3DDATEVALUE(%226%2F20%2F2020%22)))%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((B2%3AB317%26gt%3B%3DDATEVALUE(%221%2F1%2F2020%22))*(B2%3AB317%26lt%3B%3DDATEVALUE(%226%2F20%2F2020%22)))%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20an%20adaptation%20of%20these%20formulas%20that%20would%20count%20the%20number%20of%20occurrences%20in%20Column%20A%20from%2007-01-19%20to%2006-30-20%2C%20where%20the%20occurrence%20is%20prior%20to%2009-01-19%20and%20received%20from%20after%2009-01-19%20to%2006-30-20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558871%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559190%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Product%20%2F%20Date%20Range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559190%22%20slang%3D%22en-US%22%3EI%20believe%20this%20is%20the%20solution%3A%3CBR%20%2F%3E%3DSUMPRODUCT(((A2%3AB317%26gt%3B%3DDATEVALUE(%227%2F1%2F2019%22))*(B2%3AB317%26lt%3B%3DDATEVALUE(%226%2F30%2F2020%22)*(A2%3AB317%26lt%3B%3DDATEVALUE(%229%2F1%2F2019%22)))))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559300%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Product%20%2F%20Date%20Range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174625%22%20target%3D%22_blank%22%3E%40Thomas%20Hochard%3C%2FA%3E%26nbsp%3B%20you%20answer%20doesn't%20seem%20to%20meet%20what%20you%20requested.%26nbsp%3B%20Here%20is%20my%20options%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(A2%3AA317%2C%22%26lt%3B%3D%22%26amp%3BDATEVALUE(%222019-09-01%22)%2CB2%3AB317%2C%22%26gt%3B%3D%22%26amp%3BDATEVALUE(%222019-09-01%22)%2CB2%3AB317%2C%22%26lt%3B%3D%22%26amp%3BDATEVALUE(%222020-06-30%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20the%20attached%20I%20added%20a%20table%20where%20you%20can%20enter%20start%2Fend%20dates%20for%20each%20for%20various%20counts.%26nbsp%3B%20You%20also%20can%20just%20use%20the%20filter%20(down%20arrow)%20on%20the%20table%20to%20filter%20by%20date%20ranges%20and%20then%20just%20look%20at%20the%20ribbon%20on%20the%20bottom%20to%20see%20how%20many%20records%20are%20found.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559427%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Product%20%2F%20Date%20Range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174625%22%20target%3D%22_blank%22%3E%40Thomas%20Hochard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20I'd%20do%20not%20recommend%20to%20use%20in%20formulas%20components%20like%26nbsp%3B%3CSPAN%3EDATEVALUE(%227%2F1%2F2019%22).%20It's%20always%20better%20to%20keep%20all%20parameters%2C%20as%20dates%20in%20this%20case%2C%20in%20separate%20cells.%20Even%20if%20hardcode%20within%20the%20formula%20it's%20better%20to%20use%20DATE(2019%2C7%2C1).%26nbsp%3B%20DATEVALUE%20with%20the%20text%20as%20above%20could%20not%20work%20in%20international%20environment.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

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