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, Jun 07 '21 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, Jun 07 '21 at 21:48
You've got the formula going into `Range("O9")` but FillDown targetting `Range("I9")`. Is this intended and correct? — Toddleson, Jun 07 '21 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, Jun 07 '21 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, Jun 07 '21 at 22:17

score 1 · Answer 1 · answered Jun 07 '21 at 23:00

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

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 — Drew101, Jun 08 '21 at 00:15

Toddleson · Answer 2 · 2021-06-07T22:20:17.857

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

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. — Toddleson, Jun 07 '21 at 22:23
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.. — Drew101, Jun 07 '21 at 23:19

score 0 · Answer 3 · edited Jun 08 '21 at 00:44

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

`[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. — Tim Williams, Jun 08 '21 at 00:45

score 0 · Answer 4 · answered Jun 08 '21 at 09:11

0

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

Change

Sheets("Summary")

to:

Sheets(""Summary"")

answered Jun 08 '21 at 09:11

Daniel Serra

15
2

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

Update

4 Answers4