Is there a better way? I'm getting #UNPARSEABLE error.
Happy Holidays! I'm trying to use COUNTIFS formula to count the projects in a referenced sheet with a Start Date OR End Date that has the YEAR 2025. And include additional filter/condition of Level = 4.
=COUNTIFS({UK Direct Investment Level}, 4, {UK Direct Investment Start Date}, IF(YEAR(@cell) = 2025 OR ({UK Direct Investment End Date}, IF(YEAR(@cell) = 2025))))
Best Answers
-
Looks like there is a missing closing curly bracket } at the end of the very last {Cross Sheet Reference}.
-
In that case the logic of looking for start dates and end dates that happen before Jan 1, 2026 is still not going to get you those results. If you want to count how may projects have at least one day in 2025, you would use something more along the lines of…
=COUNTIFS({Status}, status criteria, {Level}, level criteria, {Start Date}, @cell <= DATE(2025, 12, 31), {End Date}, @cell >= DATE(2025, 1, 1))
Answers
-
Hello @Jennifer Sullivan
Try this:
=COUNTIFS({UK Direct Investment Level}, 4, {UK Direct Investment Start Date}, YEAR(@cell) = 2025 ,{UK Direct Investment End Date}, YEAR(@cell) = 2025))
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Thanks @Melissa Yamada for your reply. Unfortunately, it did not work. Still #Imparseable.
-
Putting them both in the same COUNTIFS even with the proper syntax is essentially using an AND function. To get something that would look at one or the other, you would need somethign more like
=COUNTIFS(first column) + COUNTIFS(second column) - COUNTIFS(first and second column)
=COUNTIFS({UK Direct Investment Level}, @cell = 4, {UK Direct Investment Start Date}, IFERROR(YEAR(@cell), 0) = 2025) + COUNTIFS({UK Direct Investment Level}, @cell = 4, {UK Direct Investment End Date}, IFERROR(YEAR(@cell), 0) = 2025) - COUNTIFS({UK Direct Investment Level}, @cell = 4, {UK Direct Investment Start Date}, IFERROR(YEAR(@cell), 0) = 2025, {UK Direct Investment End Date, IFERROR(YEAR(@cell), 0) = 2025)
If the above does not work, please provide a screenshot of the formula open in the sheet as if you are about to edit it.
-
@Paul Newcome - I tried something like you suggested (without the IFERROR). See below. Got #Invalid Data Type error. I will try your suggestion.
-
@Paul Newcome yep I missed the OR criteria there. thanks!
@Jennifer Sullivan you forgot to add the "YEAR(@cell)=2025" on each criteria.
=COUNTIFS({UK Direct Investment Level}, 4, {UK Direct Investment Start Date}, IFERROR(YEAR(@cell), 0) = 2025) + COUNTIFS({UK Direct Investment Level}, 4, {UK Direct Investment End Date}, IFERROR(YEAR(@cell), 0) = 2025) - COUNTIFS({UK Direct Investment Level}, 4, {UK Direct Investment Start Date}, IFERROR(YEAR(@cell), 0) = 2025, {UK Direct Investment End Date, IFERROR(YEAR(@cell), 0) = 2025)
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
What about what I actually posted with the @cell references and IFERROR statements all exactly as is (should only need to make sure {cross sheet references} are correct).
-
@Paul Newcome - I did try your suggestion exactly as you posted. Cross sheet references are correct. Still getting #unparseable error. See formula:
-
Looks like there is a missing closing curly bracket } at the end of the very last {Cross Sheet Reference}.
-
@Paul Newcome - Oh my goodness! That was it! Thank you so much!
-
@Paul Newcome - Could you look at this formula? I'm getting #Incorrect Argument Set. I need the formula to count the number of projects that meet this logic:
Start Date < 01/01/2026 AND Level = 4 AND Status = "Not Started" OR
End Date < 01/01/2026 AND Level = 4 AND Status = "Not Started"
=COUNTIFS({Canada Roadmap Level}, @cell = 4, {Canada Roadmap Status1}, @cell = "Not Started", {Canada Roadmap Start Date}, IFERROR(DATE(@cell ) < DATE(2026, 1, 1)) + COUNTIFS({Canada Roadmap Level}, @cell = 4, {Canada Roadmap Status1}, @cell = "Not Started", {Canada Roadmap ED}, IFERROR(DATE(@cell ) < DATE(2026, 1, 1)) - COUNTIFS({Canada Roadmap Level}, @cell = 4, {Canada Roadmap Status1}, @cell = "Not Started", {Canada Roadmap Start Date}, IFERROR(DATE(@cell ) < DATE(2026, 1, 1)), COUNTIFS({Canada Roadmap Level}, @cell = 4, {Canada Roadmap Status1}, @cell = "Not Started", {Canada Roadmap ED}, IFERROR(DATE(@cell ) < DATE(2026, 1, 1))))))@cell
-
Before we get into all of the details of the existing formulas, lets first revisit the logic of what you need… Anything with an End Date before Jan 1, 2026 is going to also have a Start Date before Jan 1, 2026. So really, all you need is a single COUNTIFS looking at the Start Dates for this particular one.
-
That would be great if it is that simple. Ultimately, I want to count the total projects we have to work on in 2025. I have projects that started in 2024; I do not want to count the 2024 projects that completed, but I do want to count the projects that have not completed and will be continued in 2025. They can be identified by their Status. This first formula is for projects Not Started. I will need to repeat the formula for each of the status types (On Track, Off Track, At Risk, Completed). Ultimately, these metrics will produce a chart on my dashboard. Does that help?
-
In that case the logic of looking for start dates and end dates that happen before Jan 1, 2026 is still not going to get you those results. If you want to count how may projects have at least one day in 2025, you would use something more along the lines of…
=COUNTIFS({Status}, status criteria, {Level}, level criteria, {Start Date}, @cell <= DATE(2025, 12, 31), {End Date}, @cell >= DATE(2025, 1, 1))
-
Thank you. I did something like that initially, but I also have projects in years 2026, 2027, 2028 that I do not want included in the count unless the Start Date begins in 2024 or 2025. That formula would count the years beyond 2025.
I want to count all projects that will be worked on in 2025 ie
- started in 2024 but got off track and did not complete by 2024 end date
- start in 2024 and end date is 2025 or greater
- start in 2025 and end date is 2025 or greater
- don't include those that start in 2026 or greater
Thanks for your help.
Help Article Resources
Categories
Check out the Formula Handbook template!