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

JConno

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))

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!

JConno

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

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.

JConno

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

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?

JConno

Hi @bisaacs

The current formula I have is:

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

Thanks!

JConno

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

@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?

JConno

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

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.