Sorry, that was user error, I though the whole thing had copied, and it had not. this is the full formula that the VBA code outputs

=IF(SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F)=0,"",SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F))