In VBA trying to create a dynamic Sumifs Formula with multiple criterias in multiple sheets

Question

In VBA I am trying to create a sumifs formula with multiple criteria across different workbooks, but I am struggling on the syntax.

WorkbookRecut.Worksheets("Summary").Activate
Dim CountRows As Long
Dim CountRows2 As Long
CountRows = WorkbookRecut.Worksheets("Summary").Range("I" & WorkbookRecut.Worksheets("Summary").Rows.Count - 1).End(xlUp).Row
CountRows2 = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("I" & CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Rows.Count - 1).End(xlUp).Row

CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Activate
Range("O6").Formula = _
"=Sumifs(" & [WorkbookRecut].Sheets("Summary").Range("I9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("I" & CountRows).Address _
& "," & [WorkbookRecut].Sheets("Summary").Range("A9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("A" & CountRows).Address _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("K6").Address(Rowabsolute:=False) _
& "," & [WorkbookRecut].Sheets("Summary").Range("D9").Address & ":" & [WorkbookRecut].Sheets("Summary").Range("D" & CountRows).Address _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("N6").Address(Rowabsolute:=False) & ")"
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("O6:O" & CountRows2).FillDown

Update

I have updated the most recent code. The only pending issue is the workbooks aren't changing, but all else works as I want :)

The expected output would be useful here.... I'd start by defining some sheet variables eg Dim wsSumm As Worksheet: Set wsSumm = WorkbookRecut.Sheets("Summary"). That will reduce your code bulk and make it easier to work with. — Tim Williams
– Tim Williams, Commented Jun 7, 2021 at 21:32
Im trying to achieve the formula whereas Test File 4.xlsx is my variable WorkbookRecut =SUMIFS('[Test File 4.xlsx]Summary'!$I$9:$I$307,'[Test File 4.xlsx]Summary'!$A$9:$A$307,$K6,'[Test File 4.xlsx]Summary'!$D$9:$D$307,$N6) — Drew101
– Drew101, Commented Jun 7, 2021 at 21:48
You've got the formula going into Range("O9") but FillDown targetting Range("I9"). Is this intended and correct? — Toddleson
– Toddleson, Commented Jun 7, 2021 at 22:04
Thanks - dumb mistake on my part. I was less concerned about that part, but definitely saved me time for my next mistake lol,. I updated the code in the main section — Drew101
– Drew101, Commented Jun 7, 2021 at 22:08
Are you saying I need to add address after each range? It still doesnt cure the issue :( The default .address is already xlreference style, which is what I am using — Drew101
– Drew101, Commented Jun 7, 2021 at 22:17

Tim Williams · Accepted Answer · 2021-06-07 23:00:15Z

1

When creating a formula string to add to a cell you need to take into account where the different ranges are relative to the sheet where you're going to place the formula. Just calling Address() on one of the inputs may not give you what you want.

You can try something like the code below to abstract that part into a separate function:

Sub Tester()
    
    Dim wsSumm As Worksheet, wsCBM As Worksheet
    Dim lr As Long, f
    
    Set wsSumm = WorkbookRecut.Worksheets("Summary")
    Set wsCBM = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics")
    
    lr = wsSumm.Cells(Rows.Count, "I").End(xlUp).Row

    f = "=SUMIFS(" & RealAddress(wsCBM, wsSumm.Range("I9:I" & lr)) & "," & _
                     RealAddress(wsCBM, wsSumm.Range("A9:A" & lr)) & ",$K6," & _
                     RealAddress(wsCBM, wsSumm.Range("D9:D" & lr)) & ",$N6)"
    
    With wsCBM.Range("O9")
        .Formula = f
    End With
    
End Sub

'get a range address for `rngRef`,
'   suitable for use in a formula on worksheet `ws`
Function RealAddress(ws, rngRef As Range) As String
    Dim s As String
    If ws.Parent Is rngRef.Worksheet.Parent Then 'same workbooks?
        If Not ws Is rngRef.Worksheet Then s = "'" & rngRef.Worksheet.Name & "'!" 'diff. worksheets?
        s = s & rngRef.Address(True, True)
    Else
        s = rngRef.Address(True, True, external:=True) 'different workbooks
    End If
    RealAddress = s
End Function

answered Jun 7, 2021 at 23:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drew101 Over a year ago

Thanks Tim - Appreciate your help, but 'm 99% there with my updated code. Only issue pending is that I cant reference the other workbooks. Its all happening in the CashMetricsWorkbookFinal

Toddleson · Accepted Answer · 2021-06-07 22:20:17Z

0

For the formula: You're probably looking for the .Address property from each of your Ranges. Something like Range1.Address & ":" & Range2.Address To get an output like $I$9:$I$307.

But for your Ranges, you need to put the CountRows inside the Range input like WorkbookRecut.Sheets("Summary").Range("A" & CountRows) and then add the .Address to it.

I also agree with @TimWilliams that your formula code could benefit greatly in terms of readability by adding some nicknames for your worksheets.

Here is what your code would look like with those 3 things corrected:

Public CashBreaksMetricsWorkbookFinal As Workbook
Public WorkbookRecut As Workbook

Dim SumSh As Worksheet
Set SumSh = WorkbookRecut.Sheets("Summary")

Dim CountRows As Long
CountRows = SumSh.Range("I" & SumSh.Rows.Count - 1).End(xlUp).Row

Dim CSCIG As Worksheet
Set CSCIG = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics")

CSCIG.Activate
Range("O9").Formula = _
       "=Sumifs(" & SumSh.Range("I9") & ":" & SumSh.Range("I" & CountRows).Address _
       & "," & SumSh.Range("A9").Address & ":" & SumSh.Range("A" & CountRows).Address _
       & "," & CSCIG.Range("K6").Address _
       & "," & SumSh.Range("D9").Address & ":" & SumSh.Range("D" & CountRows).Address _
       & "," & CSCIG.Range("N6").Address & ")"
       
CSCIG.Range("O9").FillDown

edited Jun 7, 2021 at 22:20

answered Jun 7, 2021 at 22:14

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

2 Comments

Toddleson Over a year ago

I wrote a little hastily and messily and have made several small edits. Please try again with my latest updated code if an earlier edit didn't work.

Drew101 Over a year ago

Thanks @toddleson - I think we also need a .address after the first part of the sum range criteria. Also, the code is not switching between workbooks oddly..

Tim Williams · Accepted Answer · 2021-06-08 00:44:16Z

0

We were missing .Address(External:=True)

Thanks all for helping me get there (Finally!)

Final Code Below

Public CashBreaksMetricsWorkbookFinal As Workbook
Public WorkbookRecut As Workbook

Dim CountRows As Long
Dim CountRows2 As Long

CountRows = WorkbookRecut.Worksheets("Summary").Range("I" & WorkbookRecut.Worksheets("Summary").Rows.Count - 1).End(xlUp).Row
CountRows2 = CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("I" & CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Rows.Count - 1).End(xlUp).Row

CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Activate
Range("O6").Formula = _
"=Sumifs(" & [WorkbookRecut].Sheets("Summary").Range("I9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("I" & CountRows).Address(External:=True) _
& "," & [WorkbookRecut].Sheets("Summary").Range("A9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("A" & CountRows).Address(External:=True) _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("K6").Address(Rowabsolute:=False) _
& "," & [WorkbookRecut].Sheets("Summary").Range("D9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("D" & CountRows).Address(External:=True) _
& "," & [CashBreaksMetricsWorkbookFinal].Worksheets("CSCIG_Cash Breaks Metrics").Range("N6").Address(Rowabsolute:=False) & ")"
CashBreaksMetricsWorkbookFinal.Worksheets("CSCIG_Cash Breaks Metrics").Range("O6:O" & CountRows2).FillDown

edited Jun 8, 2021 at 0:44

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

answered Jun 8, 2021 at 0:39

Drew101

637 bronze badges

2 Comments

Tim Williams Over a year ago

[WorkbookRecut].Sheets("Summary").Range("I9").Address(External:=True) & ":" & [WorkbookRecut].Sheets("Summary").Range("I" & CountRows).Address(External:=True)

would be better as WorkbookRecut.Sheets("Summary").Range("I9:I" & CountRows).Address(External:=True) You only need one range, and the [] are redundant.

Drew101 Over a year ago

Thanks Tim for the additional comments :)

Daniel Serra · Accepted Answer · 2021-06-08 09:11:01Z

0

In the formula, you have to double-quote existing quotes:

Change

Sheets("Summary")

to:

Sheets(""Summary"")

answered Jun 8, 2021 at 9:11

Daniel Serra

251 silver badge3 bronze badges

Collectives™ on Stack Overflow

In VBA trying to create a dynamic Sumifs Formula with multiple criterias in multiple sheets

Update

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related