Distinct Counts with dates and multiple criteria
Hi all,
I am hoping someone can help me out with my test tracking grid. I am trying to create multiple formulas for our tracking reports and seem to be falling short.
Above is a screen shot of our testing page. I need to get a count of the following:
- Count of test ID's that had at least one test completed - For this example the answer should come back as 7 because we only want to count Test ID 1606 once.
- Count of Distinct Test ID's with a result over 8 at the most recent schedule date - For this example the answer should come back as 1 because only Test ID 1624 had a result over 8 at their most recent schedule date. If the Test ID had a result over 8 at an earlier date I don't want to count it.
I don't know if I need to create helper columns or change the tracking form to make the Test ID the parent and then list each schedule date as a child row or what, but I can't seem to get the formulas to work. I am admittedly not great with more complex formulas - still learning. Any assistance is greatly appreciated!
Answers
-
=count(distinct([test id]:[test id])) should get you the first one ...
I will take a run at the second one later ... a little trickier.
dm
-
Thank you! That second one is proving to be a pain. I might need to change the way we report that - or maybe add some sort of helper column somehow. Thanks for your help on this!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!