Find Latest Date for With a criteria in rows

Carloscmatos · 01/02/22

I have a form that my field team inputs the action they make in customer, the same form is used for every customer, In Another sheet, I have the visit controle, and in this other sheet, I want to know the last visit my team made to each customer.

So I need to use a MAX formula for each specific customer.

The only solution I found was to user forms to enter the response in the upper rows, so my Vlookup brigs the latest entered visit, but it is not failure proof, so I would like the formula to look for the latest instead.

Thanks,

Devin Lee · 01/02/22

Hey @Carloscmatos,

Try this formula:

=MAX(COLLECT({Ref 1:Date:Date}, {Ref 2:Customer:Customer}, [Customer]@row))

Devin Lee · 01/02/22

Hey @Carloscmatos,

Try this formula:

=MAX(COLLECT({Ref 1:Date:Date}, {Ref 2:Customer:Customer}, [Customer]@row))

Jeff Reisman · 01/03/22

Hi @Carloscmatos

I wrote this up back in 2020 when I faced a similar challenge. I show my flawed solution and then explain how the correct solution I found works. Mine was for finding earliest date, but I could easily be adapted to find latest date. Perhaps it will help you.

https://community.smartsheet.com/discussion/69081/find-the-row-with-the-earliest-created-entered-date-that-matches-certain-criteria#latest

Carloscmatos · 01/04/22

https://community.smartsheet.com/discussion/comment/314630#Comment_314630

Thanks for both of you who answered the question, but @Devin Lee was simple and got the job done perfectly!

Find Latest Date for With a criteria in rows

Best Answer

Answers

Help Article Resources

Categories