Offer SUMPRODUCT, not refer to whole range

=SUMPRODUCT(('CORE Process Audit data'!$D$1:$F$1000=G5)*('CORE Process Audit data'!$G&1:$G$1000<=TODAY())*('CORE Process Audit data'!$G$1:$G$1000>=TODAY()-30))
If it does not work, try to upload a samle spreadsheet.