Formula Error When Sorting

[carl]

I have this formula:

=(SUMPRODUCT(--($E$43:$E$30001=549);--($J$43:$J$30001=$N2);--($G$43:$G
$30001="SailDirectedOrderAcceptation");--(LEFT($C$43:$C$30001;2)=C$5);
$A$43:$A$30001))/(SUMPRODUCT(--(MID($F$43:$F$30001;2;3)="549");--($J
$43:$J$30001=$N2);--($G$43:$G$30001="SailDirectedOrderNotice");--
(LEFT($C$43:$C$30001;2)=C$5);$A$43:$A$30001))

When I sort my data in Col N, I get a #REF! Error


=(SUMPRODUCT(--($E$43:$E$30001=549);--($J$43:$J$30001=#REF!);--($G
$43:$G$30001="SailDirectedOrderAcceptation");--(LEFT($C$43:$C
$30001;2)=C$5);$A$43:$A$30001))/(SUMPRODUCT(--(MID($F$43:$F
$30001;2;3)="549");--($J$43:$J$30001=#REF!);--($G$43:$G
$30001="SailDirectedOrderNotice");--(LEFT($C$43:$C$30001;2)=C$5);$A
$43:$A$30001))

Is there any way to avoid this ?

 

[Don Guillett]

I have this formula:

=(SUMPRODUCT(--($E$43:$E$30001=549);--($J$43:$J$30001=$N2);--($G$43:$G
$30001="SailDirectedOrderAcceptation");--(LEFT($C$43:$C$30001;2)=C$5);
$A$43:$A$30001))/(SUMPRODUCT(--(MID($F$43:$F$30001;2;3)="549");--($J
$43:$J$30001=$N2);--($G$43:$G$30001="SailDirectedOrderNotice");--
(LEFT($C$43:$C$30001;2)=C$5);$A$43:$A$30001))

When I sort my data in Col N, I get a #REF! Error

=(SUMPRODUCT(--($E$43:$E$30001=549);--($J$43:$J$30001=#REF!);--($G
$43:$G$30001="SailDirectedOrderAcceptation");--(LEFT($C$43:$C
$30001;2)=C$5);$A$43:$A$30001))/(SUMPRODUCT(--(MID($F$43:$F
$30001;2;3)="549");--($J$43:$J$30001=#REF!);--($G$43:$G
$30001="SailDirectedOrderNotice");--(LEFT($C$43:$C$30001;2)=C$5);$A
$43:$A$30001))

Is there any way to avoid this ?

Perhaps $N$2