SUMIFs macro with specific find values

hrh_dash 

Try this:

Sub calTotal()
    
    Dim DestwsR     As Worksheet       'DC1 & DC2 Performance 2022
    Dim DestwbR     As Workbook        'DC1 & DC2 Performance 2022
    Dim DestwsP     As Worksheet       'DC collection performance
    Dim DestwbP     As Workbook        'DC collection performance
    
    Dim CMS_start   As Long
    Dim CMS_end     As Long
    Dim SheetName   As String
    Dim sSheet1     As String
    Dim sSheet2     As String
    Dim sFormula    As String
    
    Set DestwbR = Workbooks.Open("\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\DC1 & DC2 Performance - 2022.xlsx")
    Set DestwsR = DestwbR.Sheets(Sheets.Count)
    SheetName = DestwsR.Name
    
    Set DestwbP = Workbooks.Open("C:\Users\hrhquek\desktop\DC collection Performance 2021.xlsx")
    Set DestwsP = DestwbP.Sheets("DC1")
   
    CMS_start = DestwsP.Range("B:B").Find(What:="CMS", After:=DestwsP.Range("B1")).Row
    CMS_end = DestwsP.Range("B:B").Find(What:="CMS", After:=DestwsP.Range("B1"), SearchDirection:=xlPrevious).Row
    
    sSheet1 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]DC1'!"
    sSheet2 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]" & SheetName & "'!"
    sFormula = "=SUMIFS(" & sSheet1 & "$F" & CMS_start & ":$F" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & "$B$5," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""&" & sSheet2 & "$D$5)"
    
    With DestwsR.Range("I7")
        .Formula = sFormula
        .Value = .Value
    End With
    
End Sub