Using COUNTIFS to calculate the number of tasks between two date periods
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
-
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!
-
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!
-
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!
-
@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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!