Formula Help - Completed Projects Within FY

Lchicklis
Lchicklis ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi there,

I am looking to include a metrics widget on my portfolio dashboard for all completed projects in FY20. Our fiscal year runs 2/1/19 through 1/31/20 (or alternately, this could just be completed projects in Feb 2019 and after, then I will start a new count next Feb). 

My current baseline formula is: =COUNTIF(State:State, "Complete")

Would love any help or suggestions from the community on how to incorporate the dates piece into this formula. 

Thank you!

Laura

 

Tags:

Comments

  • I would break it up in your pull up based on year using countifs.

    Make 3 new columns:

    One will be the date column that you are basing your fiscal year on. IE a system column like created or a column you use to mark something complete. 

    the second one would be a Month on with the formula =MONTH(Date@row) (Date is your first column)

    The third would be the same as the second only with the year =YEAR(Date@row)

    Then on your Pull up sheet you would count by month.Then add them how you need it for your matrix. 



    Does that help?

    Michael

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something along the lines of...

     

    =COUNTIFS(State:State, "Complete", [Completed Date]:[Completed Date], AND(ISDATE(@cell), @cell >= DATE(2019, 2, 1), @cell <= DATE(2020, 1, 31)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!