SOLVED

Iron Contributor

# 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,">"&'[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``````

19 Replies

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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``````

# Re: SUMIFs macro with specific find values

@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``````

# Re: SUMIFs macro with specific find values

``````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``````

# Re: SUMIFs macro with specific find values

@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?

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

@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``

best response confirmed by hrh_dash (Iron Contributor)
Solution

# Re: SUMIFs macro with specific find values

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``````

# Re: SUMIFs macro with specific find values

@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!

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

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

# Re: SUMIFs macro with specific find values

, thank you for the help..