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

Answer 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

Answer 2

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

Answer 3

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

Answer 4

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

Change

Sheets("Summary")

to:

Sheets(""Summary"")