SUMIFs macro with specific find values

[hrh_dash]

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,">"&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$2:$C$74,"<="&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)

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
    
    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)
    
    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
    
    DestwsR.Range("I7") = WorksheetFunction.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"))
    
End Sub

appreciate the help in advance!

[#2]

@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)")

[#3]

, 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..

[#4]

@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

[#5]

@Hans Vogelaar , 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

[#6]

@hrh_dash 

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 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)
    
    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]Aug22'!"
    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

[#7]

@Hans Vogelaar , an error populated; "error 13 Type Mismatch" for the line of code below:

    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"

what type of variable should sFormula be suited for? 

[#8]

@hrh_dash 

Sorry, my mistake. It's easy to get lost in such a long formula. It should have been - as far as I can tell -

    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)"

[#9]

@Hans Vogelaar , no worries. the code works perfectly.

is it possible to include a Sheets.count in the variable below? This is because the Sheet name changes on a monthly basis. Tried to swap Aug22 to Sheets.Count and it populates an error "run time error 1004 application defined or object defined error"

sSheet2 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]'!" & Sheets.Count

[#10 · Accepted solution]

@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

[#11]

@Hans Vogelaar, thank you! the code works perfectly. I was thinking of using ws.name as well but the placement of  

& "'!" was tricky and confusing. Thanks again for the help!

[#12]

@Hans Vogelaar , i have another similar problem and would need your advice. This time round i would like to add eomonth into the formula..

Excel formula as follows:
SUMIFS('\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$V$2:$V$61,

'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$C$2:$C$61,
">"&'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]Performance 2021'!$L$2,

'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$C$2:$C$61,

"<="&EOMONTH('\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]Performance 2021'!$L$2,0))

code as follows:

    sFormula_OA_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & "$L$2," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""& EOMONTH(" & sSheet2 & "$L$2,0))"

i did a debug.print of the code to cross check with the excel formula, everything is the same but not sure why an error is populated. Not sure whether the placement is wrong or am i missing another parenthesis..

[#13]

@hrh_dash 

It looks OK, so I have no idea why it fails without seeing the workbook.

[#14]

@Hans Vogelaar , have managed to solve the issue. thanks for the help anyway

[#15]

@Hans Vogelaar , i would like to replace the specific cell reference to an Input box, therefore how should i amend or remove the double quotes?

so instead of the user to amend the cell reference on a monthly basis from the macro, the user would just enter which cells to take reference from based from the Input box

    Dim cell_ref As String
    cell_ref = InputBox("Please indicate which cell date to take reference from?")

sFormula_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & " cell_ref," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""& EOMONTH(" & sSheet2 & "  cell_ref,0))

[#16]

@hrh_dash 

You're really hell-bent on making it ever more complicated! The instruction doesn't even fit on one line anymore.

cell_ref should be outside the quotes, just like sSheet2, since it is a VBA variable.

    sFormula_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & _
        "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & _
        cell_ref & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""&" & _
        "EOMONTH(" & sSheet2 & cell_ref & ",0))"

[#17]

thanks and really appreciate your help. I am seeing stars as well..

The format of the report is making it difficult to implement the code...

[#18]

@Hans Vogelaar , really sorry to bother u again on this SUMIF formula..

I have got another SUMIF formula would like to seek your help.. Have been staring at it for a while and still have no clue whether am i missing a double quotes or am i having an extra double quotes..

My variables placement looks fine and are not double quotes  and have checked that the commas are quoted.. I am not sure why there is still an error..

    sFormula_CMS_MISC_TC = "=SUMIF(" & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<""&" & sSheet2 & cell_ref & "," & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end)"

[#19]

@hrh_dash 

The problem is right at the end:

    sFormula_CMS_MISC_TC = "=SUMIF(" & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<""&" & sSheet2 & cell_ref & "," & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & ")"

[#20]

, thank you for the help..