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
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
