CountIFs between two dates

edited 12/09/19 in Using Smartsheet
08/19/19 Edited 12/09/19

I have been tasked with creating multiple sheets that feed into reports. In going about that process the sheets are populated by Project, Customer Issues and Roadmap sheets. Currently, I am trying to count how many Customer Issues were opened up between specific dates (Q1, Q2, Q3, Q4). I currently have the sheet working with adding additional columns that are pulled from the sheets, but it will be an issue to maintain moving forward. I am trying to simplify the calculations by trying to get this formula to calculate the same items without the other columns. Below is the formula that I am currently trying and getting a number of 0 when it should be 9 as that is what it is pulling using the additional columns. 

=COUNTIFS({Customer Issues List Range 3}, <=DATE(2019, 1, 1), {Customer Issues List Range 3}, >=DATE(2019, 3, 31))

I must be something, but for the life of me can't figure it out. 

Thanks,

Dan

Comments

  • Nic LarsenNic Larsen ✭✭✭✭✭

    I think you just need to reverse the >< in your formula, currently it reads... 

    <=DATE(2019, 1, 1) less than or equal to 1/1/2019

    Try this:

    =COUNTIFS({Customer Issues List Range 3}, >=DATE(2019, 1, 1), {Customer Issues List Range 3}, <=DATE(2019, 3, 31))

  • The small things in life matter the most. Thanks for catching that. It is now working.  I appreciate the help. 

  • I have a related, but slightly different issue. I have an "old formula" that worked for me before the introduction of @row and @cell, and I have been trying to convert the formula below to use @row instead of a row number range, but everything I try either returns "0" or an formula error message.

    Basically, I want to count entries by fiscal quarter, so I'm trying to use a date range of ">=" and "<=" to calculate the number of entries (in the case below) for fiscal quarter 1.

    If someone could give the formula below a look and suggest a solution I would be incredibly appreciative. Thanks in advance.


    =COUNTIFS([Payment Received Date]1:[Payment Received Date]103, >=DATE(2019, 10, 1), [Payment Received Date]1:[Payment Received Date]103, <=DATE(2019, 12, 31))

  • Hi @Herbert Niles

    Instead of using @row or @cell, it sounds like you're just looking to reference an entire column as your range, is that correct? If so, you'll just need to remove any row reference!

    Like this:

    [Payment Received Date]:[Payment Received Date]

    Try:

    =COUNTIFS([Payment Received Date]:[Payment Received Date], >=DATE(2019, 10, 1), [Payment Received Date]:[Payment Received Date], <=DATE(2019, 12, 31))


    See: Create a Cell or Column Reference in a Formula

    Cheers,

    Genevieve

  • Thank you Genevieve.

    Worked! :-)

Sign In or Register to comment.