Forum Discussion

Thomas Hochard's avatar
Thomas Hochard
Copper Contributor
Jul 31, 2020
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")))))
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 31, 2020

    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. 

    • GrantC4's avatar
      GrantC4
      Copper Contributor
      Jan 31, 2024
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jan 31, 2024
        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.

Resources