Select Distinct Cars By Max Inpsection Date

Options

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,

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!