Select Distinct Cars By Max Inpsection Date


Need to count the number of distinct Cars by Max Inspection Date and report on their Inspection Due Date. On my Metric sheet I have a below metric defined but realized the counts were off because the Cars being counted needed to be distinct :

Current Formula:=COUNTIFS({STATS}, "In Service", {Due Date}, MONTH(@cell) = $[Month#]

Data Sheet

Based on the Data Sheet, the total cars Due for Inspection in July is 1 (Car 100). Car 101 need to be counted in August based (Max(Due Date).

I tried INDEX(COLLECT...but getting lost because I think I need to first collect distinct cars based on max date and then compare to the Month@cell.

Thanks in advance,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!