Unique Dates
My team uses SmartSheet to log daily quote entries.
I have created a separate sheet to tally their monthly quotes/sales and other data.
I have a column on the above-mentioned sheet that I would like to tally the number of "days worked" based on the dates entered (COMPLETED) for each quoter (IS), within the current month. How do I go about this?
Above is the section where the data needs to be pulled from.
I need the formula to pull 2 to show that PG worked 2 days based on this screenshot.
Answers
-
Hello Paula,
You can use a combination of nested functions - Count/Distinct/Collect. Collect will define a range of data only pertaining to PG, and the Count(Distinct()) combo will count only unique values.
=COUNT(DISTINCT(COLLECT([Completed Date]3:[Completed Date]6, IS3:IS6, ="Lidiya Shutaya")))
Your formula will look more like this:
=COUNT(DISTINCT(COLLECT([Completed]:[Completed], IS:IS, ="P__ G_____")))
You can then add on to the formula to make sure you are only looking at month 7 of the year as follows:
=COUNT(DISTINCT(COLLECT([Completed]:[Completed], IS:IS, ="P__ G_____", [Completed]:[Completed], MONTH(@cell) = 7)))
I would create a table for the whole year per person or something similar so you don't have to go in and change the month value in the formulas every month. Same thing for the IS employee, creating a table to reference for the name will reduce the need to go back and change the employee in the formula.
Hope this helps!
Lidiya Shutaya
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!