YTD calculation - 01Apr-31Mar

Joseph Aloysias

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

Can anyone help me with the formula please?

Paul Newcome

Happy to help. 👍️

Paul Newcome

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

Joseph Aloysias

No, just want to calculate project count

Paul Newcome

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

Joseph Aloysias

Thanks Paul, can you please help me with the complete formula

Paul Newcome

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

Joseph Aloysias

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

Paul Newcome

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

range, criteria, range, criteria

to include the commas between each piece.

Joseph Aloysias

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

isa.ohara

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

Joseph Aloysias

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)

Paul Newcome

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

Joseph Aloysias

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?

Paul Newcome

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.

Joseph Aloysias

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

Paul Newcome

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

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