#REF after row deletion

Hi All,

Is it possible to do the below statement:

=(COUNTIF(Sheet1!$D$7:$M$12,A7)*Sheet1!B7)+(COUNTIF(Sheet1!$D$7:$M$12,A8)*Sheet1!B8)+(COUNTIF(Sheet1!$D$7:$M$12,A9)*Sheet1!B9)

but if row 7/8/9's row was deleted or a row was added between these, would there be a way for this formula to still refer to A7/A8/A9. Currently if anything is added or deleted it messes up my calculation and puts #REF.

I have deleted Row 7 and this is my formula

=(COUNTIF(Sheet1!$D$7:$M$12,#REF!)*Sheet1!#REF!)])+(COUNTIF(Sheet1!$D$7:$M$12,A8)*Sheet1!B8)+(COUNTIF(Sheet1!$D$7:$M$12,A9)*Sheet1!B9)

Many thanks
Scott