Using COUNTIFS to calculate the number of tasks between two date periods

JConno
JConno
edited 05/30/24 in Formulas and Functions

I would like to calculate the total number of projects on an annual basis based upon the "start date" and "end date" I have in my master sheet.

The current formula I have appears to continue to sum up projects from previous years.

Example

2024 = 10 Projects to be completed

2025 = 9 Project to be completed

My formula is calculating that in 2025 there are 19 to be completed and not 9….

=COUNTIFS({Start Date}, @cell >= DATE(2025, 1, 1), {End Date}, @cell <= DATE(2025, 12, 31))

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @JConno,

    try removing the "@cell" references and see if that works. You should just be able to use the comparison operators w/o the @cell:

    =COUNTIFS({Start Date}, >= DATE(2025, 1, 1), {End Date}, <= DATE(2025, 12, 31))

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Hi @bisaacs

    Removing "@cell" reference now returns an #UNPARSEABLE error message so I am unsure if this will work.

    Interestingly, my original formula works for all dates up until 2027….

    ….formula for 2027 onwards is the same for all others….

    =COUNTIFS({Start Date}, @cell >= DATE(2027, 1, 1), {End Date}, @cell <= DATE(2027, 12, 31))

    I have at least 20 Projects ending in 2027

    Do you have any other suggestions?

    Thanks!

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @JConno,

    Strange you're getting UNPARSEABLE, as I used a similar structure w/o having the "@cell" reference in the criteria (and using separate sheet references for the ranges) and it worked for me no problem. Did you accidently delete a parenthesis or comma when removing them?

    What's the range of both the Start Date and End Date references? Are they column ranges?

    Also I'm assuming the Start Date of all the projects ending in 2027 are in 2027? I'm a little stumped as to why it's not working w/o having @cell listed.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Hi @bisaacs

    I double-checked the comma's and I still had the same issue.

    The ranges of dates are within two separate columns in the same sheet.

    With regards start/end date….some projects will begin well in advance of 2027. Example:

    Start date = 2024

    End date = 2027

    However I am trying to calculate / sum-up the amount of Projects due to end in 2027.

    Thanks for your help!

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @JConno,

    I would double check the formula because you only get an unparseable if the formula is missing a parenthesis or isn't structured correctly, could you maybe copy/paste the formula that is giving you an unparseable?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Hi @bisaacs

    The current formula I have is:

    =COUNTIFS({Start Date}, @cell >= DATE(2027, 1, 1), {End Date}, @cell <= DATE(2027, 12, 31))

    Thanks!

  • Hi @Paul Newcome ,

    Wondering if you are able to support here?

    I would like to calculate the total number of projects on an annual basis based upon the "start date" and "end date" I have in my master sheet.

    The current formula I have appears to continue to sum up projects from previous years.

    Example

    2024 = 10 Projects to be completed

    2025 = 9 Project to be completed

    My formula is calculating that in 2025 there are 19 to be completed and not 9….

    =COUNTIFS({Start Date}, @cell >= DATE(2025, 1, 1), {End Date}, @cell <= DATE(2025, 12, 31))

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @JConno Are you able to provide a screenshot of the source data? Have you checked your filters to ensure you are manually counting the correct rows? Are there any collapsed parent rows?

  • Hi @Paul Newcome ,

    There are no filters applied in the source data however there are many indented rows with Parent headings.

    Do you think this is having an impact?

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The indentations wouldn't impact it directly, but if you are manually counting only certain levels or if there is a parent row collapsed that is hiding rows, that could cause a discrepancy between the two counts.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!