I have:-

=SUMIF('Issue Analysis'!A:A,AND('Titles List'!A3,'Issue
Analysis'!J:J0,MAX('Issue Analysis'!H:H)),'Issue Analysis'!J:J)

Explanation of what Im trying to do:-

I want to return the value in column J that is highest value of column H,
but if the highest value of column H has a respective column J value of 0,
then look at the preceeding MAX in column H, all based upon the lookup of A3
in the column A.

Hope this makes sense.

Thanks in advance