CountIfs VBA

I am having trouble converting a SUMPRODUCT formula into a COUNTIFS. The line of code below contains the correct ranges.

 Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$B$2:$B$6713=$Q$5),--(Panel!$C$2:$C$6713<=$R$5),--(Panel!$D$2:$D$6713>$R$5)),VLOOKUP($R$5,Panel!$C$2:$E$6713,3,1),""No"")"

Here is the original COUNTIFS formula:

 =IF(COUNTIFS( A$2:A$6713,F5,B$2:B$6713,"<="&G5,C$2:C$6713, ">="&G5),VLOOKUP(G5,$B$2:$D$6713,3,1),"No")

My attempt errors on the underlined line. (object defined or application defined error)
VB

 Sub Calculations()
Dim l As Long
Dim Res As Variant

    Res = Application.VLookup(Range("A2").Value, Range("CA:CF"), 6, 0)

    Const myEpilepsy As String = "=IF(COUNTIFS(--(Panel!$B$2:$B$6713=$Q$5),--(Panel!$C$2:$C$6713<=$R$5),--(Panel!$D$2:$D$6713>$R$5)),VLOOKUP($R$5,Panel!$C$2:$E$6713,3,1),""No"")"
    Const myMarfan As String = "=IF(COUNTIFS(--(Panel!$B$25610:$B$29333=$Q$5),--(Panel!$C$25610:$C$29333<=$R$5),--(Panel!$D$25610:$D29333>$R$5)),VLOOKUP($R$5,Panel!$C$25610:$E$29333,3,1),""No"")"

    If Not IsError(Res) Then
         l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
         
        Select Case Res
            Case "Comprehensive Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).Formula = myEpilepsy
                 
            Case "Marfan Disorder"
               Sheets("annovar").Range("AQ5:AQ" & l).Formula = myMarfan
        End Select

End If
End Sub

Thank you.

All the values are no longer on one sheet.

VB

 Const myEpilepsy  = "=IF(COUNTIFS( Panel!B2:B6713,Q5,C2:C6713,""<=""&R5,Panel!D2:D6713, "">=""&R5),VLOOKUP(R5,Panel!C2:$Panel!6713,3,1),""No"")"

? Thanks.

When I use that code instead, I get an objecy or application defined error on the underlined line. Thanks.

VB

 Sub Calculations()
Dim l As Long
Dim Res As Variant

    Res = Application.VLookup(Range("A2").Value, Range("CA:CF"), 6, 0)

    
    Const myEpilepsy  = "=IF(COUNTIFS( Panel!B2:B6713,Q5,C2:C6713,""<=""&R5,Panel!D2:D6713, "">=""&R5),VLOOKUP(R5,Panel!C2:$Panel!6713,3,1),""No"")"

    Const myMarfan As String = "=IF(SUMPRODUCT(--(Panel!$B$25610:$B$29333=$Q$5),--(Panel!$C$25610:$C$29333<=$R$5),--(Panel!$D$25610:$D29333>$R$5)),VLOOKUP($R$5,Panel!$C$25610:$E$29333,3,1),""No"")"

    If Not IsError(Res) Then
         l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
         
        Select Case Res
            Case "Comprehensive Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).Formula = myEpilepsy
                 
            Case "Marfan Disorder"
               Sheets("annovar").Range("AQ5:AQ" & l).Formula = myMarfan
        End Select

End If
End Sub

It is not the formula line that throws an error it is this line: Thanks.

 Sheets("annovar").Range("AQ5:AQ" & l).Formula = myEpilepsy

Thank you Norie , it is working now. I has an extra space and a $ in the formula.