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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!