Reference variable worksheet in another workbook in formula SUMIFS using VBA

Question

I am trying to add a SUMIFS formula to cells, whereby I need to have variable which refers to another sheet in another workbook. However, this does not work in my code. FYI, I have already opened the workbook with msoFileDialogFilePicker.

Here is the part I have to add the formula, ws is an object of worksheet:

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
    .Title = "..."
    .AllowMultiSelect = False

    If .Show = True Then
        If .SelectedItems(1) <> vbNullString Then
            Filename = .SelectedItems(1)
        End If
    Else
        Exit Sub
    End If
    Set wb = GetObject(Filename)
    Set ws = wb.Sheets("ASEAN (EUR)")

    With ws
        YTDws.Range("$A$1:$D$" & usedRow).AutoFilter Field:=1, Criteria1:="Singapore"
        If .Range("Q20").Value = "SINGAPORE" Then
        usedRow2 = YTDws.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range("C2:C" & usedRow2)
        For Each cl In rng.SpecialCells(xlCellTypeVisible)
            cl.Select
            cl.FormulaR1C1 = "=SUMIFS('&ws.name&'!R[32]C[3]:R[243]C[3],'&ws.name&'!C[2], C[-1])"
        Next cl
        End If
    End With

End With

I believe the problem lies with this line of code:

cl.FormulaR1C1 = "=SUMIFS('&ws.name&'!R[32]C[3]:R[243]C[3],'&ws.name&'!C[2], C[-1])"

I have searched online of many examples of doing and this one I got it from this post, where I found it similar to my case. However, it seems still not working, the program prompts me to select sheets from the workbook and saying it cannot find the worksheet ws.name. After I select the worksheet, the formula looks like this in the cell: =SUMIFS('[&ws.name&]ASEAN (EUR)'!R[32]C[3]:R[243]C[3],'[&ws.name&]ASEAN (EUR)'!C[2], C[-1]).

Please help me to locate the problem, thanks in advance!

Answer 1

Couple of things

1. Which sheet are you referring to here Set rng = Range("C2:C" & usedRow2)
2. The SumIfs() syntax is =SUMIFS(Sum_range,Criteria_range1,Criteria1 ....).
3. When using SUMIFS() with an external workbook, the formula looks like this

"=SUMIFS('[" & wb.Name & "]" & ws.Name & "'!" & YOUR_SUM_RANGE & ",'[" & wb.Name & "]" & ws.Name & "'!" & YOUR_CRITERIA_RANGE & "," & YOUR_CRITERIA & ")"

You are only using the worksheet name and not the workbook name.

My suggestion: Manually open the workbook and enter the formula (again manually). Check if the formula works and then use that formula in your code