Ignore #N/A in sumif and average if

You can use array formulas like these to ignore any errors in the sum/average ranges

=SUM(IF($B$2:$B$86=A89,IF(ISNUMBER($G$2:$G$86),$G$2:$G$86)))

=AVERAGE(IF($B$2:$B$86=A89,IF(ISNUMBER($H$2:$H$86),$H$2:$H$86)))

In that latter formula it actually just ignores errors, if you really want to treat errors as zero then the result will be different because those zeroes will give you a different average, for that version you can use this slightly amended formula:

=AVERAGE(IF($B$2:$B$86=A89,IF(ISNUMBER($H$2:$H$86),$H$2:$H$86,0)))

all formulas need to be confirmed with CTRL+SHIFT+ENTER