COUNT(DISTINCT(COLLECT to count unique values based on criteria


I tried using the COUNT(DISTINCT(COLLECT function to determine how many unique values based on a Date column. I need to count the unique Batch Numbers that were Received on Date = TODAY.

This is the formula I came up with: =COUNT(DISTINCT(COLLECT({Batch Number}, {Date: Received}, TODAY())))

It returns 1, which I know is incorrect. It seems to work when I add days before or after TODAY. For example, when I count the unique Batch Numbers Received TODAY(-4), it returns the correct number.

Any ideas?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!