Inconsistent results from COUNT(DISTINCT(COLLECT


I am using a separate sheet to calculate unique names (employees/visitors) who have visited the respective locations, separated by morning and afternoon sessions. I was using Sheet Summary in the source sheet but the numbers don't refresh by itself for some reasons, hence I have created a "metrics" sheet. The results are shown as Metrics in a dashboard.

For employees, this is the formula:

=COUNT(DISTINCT(COLLECT({Form Name}, {Form Work Location}, "Building A", {Form Date}, TODAY(), {Form Session}, "Morning")))

1st Problem: Somehow, the result for Building A is not accurate though it's ok for Building B. I have no idea why.

For non-employees, this is the formula:

=COUNT(DISTINCT(COLLECT({Form Visitor Name}, {Form Visitor Location}, "Building A", {Form Date}, TODAY())))

2nd Problem: The results are accurate for non-employees but the numbers seem to change by itself occasionally, not sure if it's because there are new records added in the source sheet. I have to go into the metrics sheet to refresh (pressing F5) and save the file before it will show accurately in the dashboard.

Any advice?


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @firestorm ,

    I suspect your issue is caused by the time stamp in your submission date column. The dates and times look right by when used in a formula produce UTC results not local time. To check it, insert a column [date only] and insert the column formula =DATEONLY([Submission Date]@row). You'll likely find that some of the dates returned don't match your submission column.

    There are some techniques to correct that if you find it's the issue.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!