MAXIFS or MAX on Non-adjacent Cells with Conditions

Hi, I've been searching and reading for about 3 days now and can't seem to figure this out. I have Excel 365 and understand that the MAXIFS function is available. However, because my situation has to consider non-adjacent cells, I can't make it work for me as this function calls for ranges, not individual cells.

I'm trying to get a max date from non-adjacent cells only if other non-adjacent cells have a specific value. For example, in a given row, I have information related to 3 work orders (WOs). Each WO has a status and a completion date. I'd like to get the max date (PM Last Completion Date (PM.LASTCOMPDATE)) for only the WOs that are in the "COMP" status. If none of the WOs are in the "COMP" status, then null. In the example provided, M2 should be 3/31/2020. As always, any help is deeply appreciated!