YTD calculation - 01Apr-31Mar
I would like to calculate YTD from 01Apr-31Mar for the In-progress & completed projects,
Can anyone help me with the formula please?
Best Answer
-
Happy to help. 👍️
Answers
-
You want to calculate YTD for what exactly? Are you trying to average the percentages?
-
No, just want to calculate project count
-
In that case you would use a COUNTIFS function. Range 1 would be the date column. Criteria 1 would be greater than or equal to the start date. Range 2 would also be the date column. Criteria 2 would be less than or equal to the end date. Range 3 would be the status column, and criteria 3 would be an OR function to look for "Complete" or "In Progress".
OR(@cell = "Complete", @cell = "In Progress")
-
Thanks Paul, can you please help me with the complete formula
-
=COUNTIFS({date range}, criteria 1, {date range}, criteria 2, {status column}, OR(@cell = "Complete", @cell = "In Progress"))
-
I tried this, but not working Can you please help
=COUNTIFS({Project Intake Sheet Range 1} <= {Project Intake Sheet Range 3} > {Project Intake Sheet Range 2}, OR(@cell = "Completed", @cell = "In Progress"))
-
Your syntax is a bit off. Make sure you are using
range, criteria, range, criteria
to include the commas between each piece.
-
YTD calculation - 01Apr-31Mar - I would like to count how many projects are completed in 01Apr to 31March
Can anyone please help me with the formula, I'm new to formulas and Smartsheet
-
You've almost got it - you need to add some commas to separate the items, and I think you need some more ranges:
Range 1: {Project Intake Sheet Range 1}
Criterion 1: @cell <= {Project Intake Sheet Range 3}
Range 2: {Project Intake Sheet Range 1}
Criterion 2: @cell > {Project Intake Sheet Range 2}
Range 3: I'm not seeing the range for this here - but it would be where you have the Project Status. If it is in the same sheet you would use: [Project Status]:[Project Status] for the range
Criterion 3: OR(@cell = "Completed", @cell = "In Progress")
=COUNTIFS({Project Intake Sheet Range 1}, @cell <= {Project Intake Sheet Range 3}, {Project Intake Sheet Range 1}, @cell > {Project Intake Sheet Range 2}, [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "In Progress"))
-
How can we set the date range - 01Apr to 31March?
I want to count the completed projects within 01Apr2022 to 31March2022(Year should change every year - it should auto calculate completed projects within the range)
-
Apologies. I stopped getting notifications on this one. Give this a try:
=COUNTIFS({date range}, @cell>= DATE(2022, 04, 01), {date range}, @cell<= DATE(2023, 03, 31), {status column}, OR(@cell = "Complete", @cell = "In Progress"))
-
this is working fine, Thank you so much @Paul Newcome
=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= DATE(2022, 4, 1), [Actual End Date]:[Actual End Date], @cell <= DATE(2023, 3, 31), [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "In Progress"))
but one concern is we need to change the year to 2022 to 2023 & 2023 to 2024 for next year, Is there a way to automatically identify year based on TODAY()/ or this year?
-
For the April date you would use YEAR(TODAY()) in the year portion of the DATE function, and then for the March date you would use YEAR(TODAY()) + 1.
-
I tried this formula but its showing "0" count, am I doing it right?
=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= YEAR(TODAY()), [Actual End Date]:[Actual End Date], @cell <= YEAR(TODAY()) + 1, [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "In Progress"))
-
The YEAR(TODAY()) would go INSIDE of the DATE function.
DATE(YEAR(TODAY()), mm, dd)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives