# YTD calculation - 01Apr-31Mar

Options
✭✭✭✭✭

I would like to calculate YTD from 01Apr-31Mar for the In-progress & completed projects,

Can anyone help me with the formula please?

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

«1

• ✭✭✭✭✭✭
Options

You want to calculate YTD for what exactly? Are you trying to average the percentages?

• ✭✭✭✭✭
Options

No, just want to calculate project count

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

=COUNTIFS({date range}, criteria 1, {date range}, criteria 2, {status column}, OR(@cell = "Complete", @cell = "In Progress"))

• ✭✭✭✭✭
Options

=COUNTIFS({Project Intake Sheet Range 1} <= {Project Intake Sheet Range 3} > {Project Intake Sheet Range 2}, OR(@cell = "Completed", @cell = "In Progress"))

• ✭✭✭✭✭✭
Options

Your syntax is a bit off. Make sure you are using

range, criteria, range, criteria

to include the commas between each piece.

• ✭✭✭✭✭
Options

`YTD calculation - 01Apr-31Mar - I would like to count how many projects are completed in 01Apr to 31March`

• ✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

The YEAR(TODAY()) would go INSIDE of the DATE function.

DATE(YEAR(TODAY()), mm, dd)