Unique Dates

Paula Gribbin

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.

Lidiya Shutaya

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