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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!