Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Sep 28, 2022
Solved

SUMIFs macro with specific find values

How should the code below be amended so that the SUMIFS macro would populate the correct values? Currently the value populated is 0   Excel formula as follows: SUMIFS($F$2:$F$74,$C$2:$C$74,">"&'[D...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Sep 29, 2022

    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

     

HansVogelaar's avatar
HansVogelaar
MVP
Sep 28, 2022

hrh_dash 

Does this work?

    DestwsR.Range("I7") = Evaluate("SUMIFS($F$" & CMS_start & ":$F$" & CMS_end & ",$C$" & CMS_start & ":$C$" & CMS_end & ","">""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$" & CMS_start & ":$C$" & CMS_end & ",""<=""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)")
  • hrh_dash's avatar
    hrh_dash
    Iron Contributor
    Sep 28, 2022
    , I think my excel formula isn’t clear. Am sorry for that. I am actually running the code on a separate workbook. Which means there are total 3 workbooks involved. DestwbR and DestwbP will not be containing the macro due to them being saved in the shared drive thus I would execute the macro from my personal workbook saved on my desktop. The evaluate macro would work but my filepath is too long..
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 28, 2022

      hrh_dash 

      And this?

          With DestwsR.Range("I7")
        .Formula = "=SUMIFS($F$" & CMS_start & ":$F$" & CMS_end & ",$C$" & CMS_start & ":$C$" & CMS_end & ","">""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$" & CMS_start & ":$C$" & CMS_end & ",""<=""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)"
        .Value = .Value
    End With
      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        Sep 29, 2022

        HansVogelaar , this would be the actual excel formula with extremely long filepath:

         

        SUMIFS('\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]DC1'!$F$2:$F$74,'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]DC1'!$C$2:$C$74,">"&'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]DC1'!$C$2:$C$74,"<="&'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5

         

        therefore is it possible that i could add variables in the macro below such as DestwsP and DestwsR?

            With DestwsR.Range("I7")
        .Formula = "=SUMIFS(DestwsP.range($F$" & CMS_start & ":F$" & CMS_end & "),DestwsP.range($C$" & CMS_start & ":$C$" & CMS_end & "),"">""& DestwsR.range("B5"),DestwsP.range($C$" & CMS_start & ":$C$" & CMS_end & "),""<=""& DestwsR.range("D5"))"
        .Value = .Value
    End With

         

         

Resources