# 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.

• ✭✭✭✭✭✭
Options

Use a combo of :

• Options

Yes, I tried and several combinations for exampe - =COUNT(DISTINCT(COLLECT({Cars}, {Due Date5}, MONTH(@cell) = \$[Month#]@row))).

• ✭✭✭✭✭✭
Options

I'm not sure I understand why car 101 is being pushed to August when it has the same due date as car 100. Or are you trying to count how many unique days there are in July that have due dates on them?

• Options

The max inspection date overrides the earlier date. Normally inspections occur every 90 days, sometimes cars are spot checked which will generate a new inspection renewal date.

• edited 07/06/22
Options

Another option would be to collect the distinct cars and add a helper column Active/Inactive to switch off the earlier inspection???

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!