It seems whenever we sort, the following formulas do not update with the correct row. In the example below the "Updates!$A4" reference stays A4 no matter what row it is sorted to. We have to have our spreadsheet set to manual calculation due to the intensive calculation process (which we only need once a day). I would also not be opposed to recommendations for rewriting this formula all together. I have tried absolute, relative and mixed, but it still stays the same.


Formula: copy to clipboard 
=IF(SUMIFS(PaceData!$E:$E,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3)=0,SUMIFS(PaceData!$D:$D,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3),TEXT(SUMIFS(PaceData!$E:$E,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3),"MM/DD/YY")&" A")