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
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
