Originally Posted by NBVC

I think you are summing the max date of each of the items that you are searching column I for... Try using Max instead of Sum

=MAX(MAXIFS('Job occurrences'!C:C,'Job occurrences'!B:B,"s"&A42,'Job occurrences'!I:I,{"Visit Completed","No Access","Action/Info/Appt Reqd","Further Appt Reqd","Job Completed","On Site"}))