COUNT(DISTINCT(COLLECT to count unique values based on multiple criteria
Hi,
I'm trying to count the dates we've worked on a job site, but we have multiple ones. Of course, at first, I'm trying to calculate just for one criterion.
So far, I've tried using the Sheet Summary: =COUNT(DISTINCT(COLLECT(Date:Date, [Site ID]:[Site ID], "CUPERTINO")))
As per my database, I have 80 entries for Cupertino, but counting the days I've worked there for 22 days, and with the formula gives 1 as the value:
Then, I tried counting the days instead of sites:
=COUNT(DISTINCT(COLLECT([Site ID]:[Site ID], Date:Date, "01/01/23")))
How can I count the days I've spent on a job site?
Best Answers
-
Hey @Denisse A
Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.
The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?
Try this
=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))
Kelly
-
Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:
=COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))
Answers
-
Hey @Denisse A
Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.
The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?
Try this
=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))
Kelly
-
Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:
=COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))
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!