Dynamic Named Ranges in Formula

Has anyone successfully used a named range across 2 sheets in an if statement? I am trying to use the below if statement on a set of data that exceeds excels limit. I thought the solution might be to use named ranges across 2 sheets however I don't seem to be able to get the formula to calculate once I extend the named range across 2 sheets. The If statement is set-up so that it will calculate based on filters in rows 1-4.

=IF(D33<100,COUNTIFS(Retention_Renewal_Status,"<>LAPSED",Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*")/COUNTIFS(Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*"),IF(COUNTIFS(Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*",Retention_Insurer,"*"&$E$2&"*",Retention_Web_Source,"*"&$F$2&"*",Retention_NCD_Band,"*"&$G$2&"*",Retention_Age_Band,"*"&$H$2&"*",Retention_Cover_Level,"*"&$I$2&"*",Retention_Payment_Type,"*"&$J$2&"*")<100,COUNTIFS(Retention_Renewal_Status,"<>LAPSED",Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*")/COUNTIFS(Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*"),COUNTIFS(Retention_Renewal_Status,"<>LAPSED",Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*",Retention_Insurer,"*"&$E$2&"*",Retention_Web_Source,"*"&$F$2&"*",Retention_NCD_Band,"*"&$G$2&"*",Retention_Age_Band,"*"&$H$2&"*",Retention_Cover_Level,"*"&$I$2&"*",Retention_Payment_Type,"*"&$J$2&"*")/COUNTIFS(Retention_Trans_Date,">="&EDATE($A$3,-2),Retention_Trans_Date,"<="&EOMONTH($A$3,0),Retention_Age_of_Rnl,"Y",Retention_Premium_Band,D$3,Retention_Brand,"*"&$C$2&"*",Retention_Source,"*"&$D$2&"*",Retention_Insurer,"*"&$E$2&"*",Retention_Web_Source,"*"&$F$2&"*",Retention_NCD_Band,"*"&$G$2&"*",Retention_Age_Band,"*"&$H$2&"*",Retention_Cover_Level,"*"&$I$2&"*",Retention_Payment_Type,"*"&$J$2&"*")))
[/I]

I'm not sure how much sense the formula makes without seeing the whole sheet.

Retention_Renewal_Status is one of the named ranges that is across 2 sheets and I get a #Value! error for the whole formula although it will let me count the named range =COUNT(Retention_Renewal_Status,"<>LAPSED")