=SUM(MAXIFS formula works in some cells and not others

[garymon9AX]

Please help - this is driving me mad. See attached spreadsheet.



Column J in the 'Network Homes' sheet, is trying to return the latest date from Column C on the 'Job Occurrences' sheet, according to certain criteria in the formula.

Can any one tell me why the cells shaded pink in column J produce silly dates when others in the same column work ok?

I am somewhat of a novice, so would appreciate any help given.

[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"}))

[garymon9AX]

Thanks NBVC. Your suggestion worked a treat.
I am most grateful.

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"}))