VBA & Macro Problems

Build an Excel VBA SUMIFS Formula with External Workbook References

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Build a SUMIFS formula in VBA that references an external workbook and uses dynamic criteria, then assign it to a destination cell.

Build a SUMIFS Formula in Excel VBA with External Workbook References
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA macro needs to sum values from another workbook using a criterion stored in the destination worksheet.
Observed behavior
The destination cell should contain a valid SUMIFS formula or a fixed calculated result.
Before you start

Open both workbooks and confirm the workbook names, worksheet names, source columns, and row limits. Save the workbook containing the macro in a macro-enabled format such as .xlsm.

Solution 1Recommended

Construct and assign the external SUMIFS formula in VBA

Use this method to leave a formula in the destination cell that recalculates when its source data or criterion changes.

The example sums column F in Source.xlsx where the corresponding value in column C is greater than or equal to Report!B5. Both source ranges use the same rows. Adjust the workbook, sheet, columns, and row limits for your data.

1
Open the VBA editor

In Excel, open the workbook that will contain the macro, then select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the macro

Paste this code into the module: Sub WriteExternalSumifs() Dim srcBook As Workbook Dim srcSheet As Worksheet Dim destSheet As Worksheet Dim srcRef As String Dim sFormula As String Dim firstRow As Long, lastRow As Long Set srcBook = Workbooks("Source.xlsx") Set srcSheet = srcBook.Worksheets("Data") Set destSheet = ThisWorkbook.Worksheets("Report") firstRow = 2 lastRow = 100 srcRef = "'[" & Replace(srcBook.Name, "'", "''") & "]" & _ Replace(srcSheet.Name, "'", "''") & "'!" sFormula = "=SUMIFS(" & srcRef & "$F$" & firstRow & ":$F$" & lastRow & _ "," & srcRef & "$C$" & firstRow & ":$C$" & lastRow & _ ","">=""&$B$5)" destSheet.Range("I7").Formula = sFormula End Sub

3
Run and check the formula

Keep Source.xlsx open, return to Excel, and run WriteExternalSumifs from Developer > Macros. Select Report!I7 and inspect the formula bar and calculated result.

Construct and assign the external SUMIFS formula in VBA
Quotation marks matter: The doubled quotation marks in the VBA string produce the ">=" criterion in the worksheet formula. The external reference uses brackets around the workbook name and apostrophes around the workbook and sheet reference.
Free Microsoft Office alternative

Work with SUMIFS spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with spreadsheet formulas as you migrate, while retaining Excel for VBA workflows that require Microsoft-specific macro behavior.

Work with SUMIFS formulas in WPS SpreadsheetsOpen and edit Microsoft Excel .xlsx filesUse a familiar spreadsheet interface for an easier transitionAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my external SUMIFS formula return an error?

Check that the source workbook is open, the workbook and sheet names are correct, and the sum and criteria ranges have matching dimensions. Select the destination cell and inspect the formula bar for misplaced apostrophes or quotation marks.

How do I use a cell as the SUMIFS criterion?

In the worksheet formula, combine the operator and cell reference, for example, ">="&$B$5. When constructing that formula in VBA, double the quotation marks inside the string.

Can I make the source row limits dynamic?

Yes. Set variables such as firstRow and lastRow before constructing the formula, then concatenate them into both the sum range and criteria range. The two ranges must cover matching rows.

How do I keep only the SUMIFS result?

After assigning the formula, calculate the workbook and set the destination cell's Value property to its current Value. This removes the formula, so the result will no longer update.

Can SUMIFS calculate from a closed source workbook?

Excel SUMIFS formulas that refer to ranges in a closed workbook can return a #VALUE! error. Open the source workbook before calculating, or use a different approach designed for closed-workbook data.