Counting Unique Data Only if Associated Column is Not Blank
Hello again! We really do research these things before we ask! ;)
We are looking to count the unique Store Numbers for "P1 Complete Dates" and "P2 Complete Dates" in the Sheet's Summary Data, so we can make sure each store is only being counted once for each phase.
The formula that we found and tried is only giving a result of 1, which we know is inaccurate.
=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date]), <>""))
Test sheet: https://app.smartsheet.com/b/publish?EQBCT=a3686cd8c2ff4bcc8047b4ba54bc30ce
Thanks for taking a look!
Best Answer
-
Try this
=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))
You had a parenthesis too early, which closed off the last criteria too soon.
Answers
-
Try this
=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))
You had a parenthesis too early, which closed off the last criteria too soon.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!