Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hi
I am trying to count the number of items that are for a particular month and year.
eg: I would like to count the number of items submitted for the month of Jan 2018
the column I am wanting to pull the data from is a date column
Hi Kim,
As per the screencap:
Month - dropdown with January, February etc.
Year - dropdown with 2017, 2018 etc. (as per your requirements)
Count - formula: =COUNTIF([Submitted Merged]:[Submitted Merged], [Search Date]1)
Submitted Date: Just a regular date field
Search Date - formula: =IF(Month1 = "January", 1 + " " + Year1, IF(Month1 = "February", 2 + " " + Year1, IF(Month1 = "March", 3 + " " + Year1, IF(Month1 = "April", 4 + " " + Year1, IF(Month1 = "May", 5 + " " + Year1, IF(Month1 = "June", 6 + " " + Year1, IF(Month1 = "July", 7 + " " + Year1, IF(Month1 = "August", 8 + " " + Year1, IF(Month1 = "September", 9 + " " + Year1, IF(Month1 = "October", 10 + " " + Year1, IF(Month1 = "November", 11 + " " + Year1, IF(Month1 = "December", 12 + " " + Year1, ""))))))))))))
Submitted Merged - formula: =IF(LEN([Submitted Date]1) > 0, MONTH([Submitted Date]1) + " " + YEAR([Submitted Date]1), "")
You can then hide the Search Date and Submitted Merged columns when you're satisfied it works OK.
I used Month & Year dropdowns to provide a nicer user experience, but you can simplify the whole thing by just asking the user to pick a date.
Hope this helps.
Hi Chris
Thank you for your help but unfortunately I cannot get the formulas to work. I keep getting an error.
I am sure I am missing a detail but can't seem to identify it.
Count formula shows a blocked error
Search Date formula shows a unparseable error
Submitted Marge formula accepts the formula but shows no results
I have created columns as per your suggestion and added the formulas to the first row of each column
Kim
Hello everyone, I'm building an automated Above Ceiling Work Permit process for our hospital and have almost everything working. I'm hoping someone can tell me if the final step is possible using native Smartsheet features. My current workflow A contractor scans a QR code and completes a Smartsheet Form. The form creates a…
I am using the new "linked" column for a dropdown on my sheet. Is there any way to sort the dropdown options? I have sorted them on my source sheet, however it doesn't update in the dropdown.
I am building out my sheet summary from a schedule. I have a Parent Row column and all of my parent rows have a checkbox. I want a formula that looks at the first checkbox and looks at the status. If status is Complete, that summary field would then indicate that the Project is in Definition. If the status is In Progress…
