Countif numbers of dates in a column between two dates

Hello,

I'm trying to automatically calculate the number of dates (in another sheet) which are between two dates in my master data sheet. I can use the COUNTIF function to find the number of dates on/after my first date using the following function:

=COUNTIF({Enquiry Tracker - Date Raised Column}, >=[Reporting To (Week Ending)]@row)

However, I want to calculate the number of dates in the same list between two 'Reporting To' dates i.e. Fri 11-Jun-2021 and Fri 25-Jun-2021.

Can anyone please help me out?

I've tried using nested COUNTIF and AND functions but I just keep getting an error message.

Best Answer

  • Stephen Barnett
    Answer ✓

    Hey everyone - thanks for your feedback on my issue and finally got a working solution based on some of the ideas above.

    Have to say this is a great community for sharing ideas/solutions between everyone. 😀

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will need to use a COUNTIFS (with the S on the end). Then you can incorporate more than one range/criteria set. In this particular case, you would specify the same range, but then you would adjust the criteria to be the other end of the date range.

  • Hey Stephen,

    I believe one way of doing this is using COUNTIFS function instead of countif, i see you were trying to use AND conditional, COUNTIFS have the AND operator implicitly so your formula should look something like this one:

    =COUNTIFS({Date}, >=DATES@row, {Date}, <DATESEND@row)

    {Date}- Would be the range where you are keeping your reporting dates.

    On my mind if you want to know how many dates you have in a date range i would recommend to have two different columns with the start date of the range and the end date of the range, please see the below picture, i hope this help, please let me know if it doesn't.

    Thanks


  • Stephen Barnett
    Answer ✓

    Hey everyone - thanks for your feedback on my issue and finally got a working solution based on some of the ideas above.

    Have to say this is a great community for sharing ideas/solutions between everyone. 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!