Trying to count if data range falls between two dates.

Options

I have a sheet with StartDate and EndDate fields. I want to count actual items opened on a separate sheet if the date falls between the start date and end date. The below formula keeps returning #UNPARSEABLE and I suspect its something minor.

=COUNTIFS({Request Tracker with Form Range 1}, "<=" & (EndDate@row),{Request Tracker with Form Range 1}, ">=" & (StartDate@row))

Best Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 06/19/24 Answer ✓
    Options
    =COUNTIFS(
    {Request Tracker with Form Range 1}, <= (EndDate@row),
    {Request Tracker with Form Range 1}, >= (StartDate@row))
    

    and make sure that the date columns and actually formatted as DATE in their properties.

    ...

  • Alex_L_Oncor
    Alex_L_Oncor ✭✭
    edited 06/19/24 Answer ✓
    Options

    =COUNTIFS({Request Tracker Form StartDate},>=[StartDate]@row,{Request Tracker Form EndDate},<=[EndDate]@row)

    I think that should work or something similar. If not a way to surely accomplish this would be to add a column to your other sheet. Make that column have a text like "Between"

    =IF(AND(DATE@row>=[StartDate]@row, DATE@row <= [EndDate]@row),"Between","")

    On the separate Sheet your equation can now be

    =COUNTIF({Between?},"Between")

    Hope this helps.


Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 06/19/24 Answer ✓
    Options
    =COUNTIFS(
    {Request Tracker with Form Range 1}, <= (EndDate@row),
    {Request Tracker with Form Range 1}, >= (StartDate@row))
    

    and make sure that the date columns and actually formatted as DATE in their properties.

    ...

  • Alex_L_Oncor
    Alex_L_Oncor ✭✭
    edited 06/19/24 Answer ✓
    Options

    =COUNTIFS({Request Tracker Form StartDate},>=[StartDate]@row,{Request Tracker Form EndDate},<=[EndDate]@row)

    I think that should work or something similar. If not a way to surely accomplish this would be to add a column to your other sheet. Make that column have a text like "Between"

    =IF(AND(DATE@row>=[StartDate]@row, DATE@row <= [EndDate]@row),"Between","")

    On the separate Sheet your equation can now be

    =COUNTIF({Between?},"Between")

    Hope this helps.


  • Art A
    Options

    That worked, thanks! There is enough of a difference in syntax from what I'm use to I get stuck on some trivial stuff sometimes.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!