YTD calculation - 01Apr-31Mar

Joseph Aloysias
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?



Best Answer

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

    No, just want to calculate project count

  • Paul Newcome
    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
    Joseph Aloysias ✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Joseph Aloysias
    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
    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
    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

  • 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
    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
    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
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭

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


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