Total # of Assigned Projects per Month in from the "Assignment Date" column

This discussion was created from comments split from: COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?).

Answers

  • Ena
    Ena ✭✭
    edited 06/30/23

    @Paul Newcome Hi there, good morning 🙋‍♀️

    I am a Smartsheet newbie and I would like to know if you can help me figure out the correct formula to return the Count of # of Months in a column = Assignment Date (date range = mm/dd/yyyy).

    I have a Summary column at the right most part of the sheet, where I put all the formulas and calculations, wherein I use this as a source later on to create a chart widget in another dashboard.

    Here's the column I am referencing to:

    And this is where I want to input the formula: Result column (right most part of the same sheet)

    The dates on the Calculation column are manual inputs (Jan 2022, Feb 2022, and so on..) Because I will be using this data as my source later on in creating a widget chart to a dashboard linked to this sheet

    I just want to get the Total # of Assigned Projects per Month in from the "Assignment Date" column. I have tried many formulas I found on this forum but none works and returns as #UNPARSEABLE. 😥 I really hope you could help me.

    Thank you in advance, Paul! 😊

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ena You would use something like this:

    =COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022)))


    The above would be for Jan 2022. You would adjust the 1 for the appropriate month number and the 2022 for the appropriate year as needed.

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Ena,

    I would suggest that to create one helper column for Months, =IFERROR(MONTH([Assignment Date]@row), ""), I am assuming in the Result column each cell represents each month or you can create summary fields for each month then apply this formula =COUNTIF(Month: Month, 1), for each month update the month number, for example, 1 for January, 2 for February, etc.

    I hope this works for you.

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Ena
    Ena ✭✭

    This did the trick! Wow, thanks so much @Paul Newcome, I appreciate you taking the time to help out a newbie here ☺️ One thing to also consider, why my previous formulas weren't working: the Date Column properties were set to "Text" --so I just changed it to "Date" and it worked wonders 😉

    Many thanks once again!

  • Ena
    Ena ✭✭

    Very helpful! Thank you so much @Kaveri Vipat --I have tried this as well and works awesome! Appreciated 🤗

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    And yes. Referenced column type must be a date type column, and it must have date values in it, but the #UNPARSEABLE error comes from syntax issues. The column/data type would have output a different error message once those syntax issues were fixed (one of the "invalid" errors although I can't remember right off exactly which).


    I find the below link pretty helpful when troubleshooting formulas:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!