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

System · 07/03/23

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

Ena · 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:

Screen Shot 2023-07-01 at 4.06.13 AM.png

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

Screen Shot 2023-07-01 at 4.13.25 AM.png

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 · 07/03/23

@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 · 07/03/23

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.

Ena · 07/07/23

Paul Newcome

Jul 3, 2023

@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.

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 · 07/07/23

Kaveri Vipat

Jul 3, 2023

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.

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

Paul Newcome · 07/10/23

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: