VBA & Macro Problems

Fix Excel VBA SUMIF Returning 0 by Qualifying Workbook and Worksheet References

Khadija KhanKhadija Khan Nov 20, 2026 869 views

Question details

Calculate SUMIF values from a source workbook and write the combined result to a specified destination worksheet.

Fix Excel VBA SUMIF Returning 0 with Qualified Workbook and Worksheet References
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A VBA macro reads criteria and sum ranges from the Book workbook and writes a total to the Destination workbook.
Observed behavior
SUMIF intermittently returns 0 or writes to an unintended sheet when ranges depend on the active workbook or worksheet.
Before you start

Open both workbooks in Excel and check their exact file and worksheet names. Identify the criteria column, sum column, criteria values, and destination column before adapting the macro.

Solution 1Recommended

Qualify the source ranges and destination cell in VBA

Use explicit workbook and worksheet objects so the calculation does not depend on which sheet is active.

The example uses open workbooks named Book.xlsx and Destination.xlsx. It sums values in column C for two criteria in column B, then appends the combined total below the existing entries in destination column B. Replace the example names, ranges, and criteria with your own.

1
Open the VBA editor

In Excel, press Alt+F11, then open the module containing your macro or choose Insert > Module.

2
Assign workbook and worksheet objects

Reference the open workbooks by their exact names and assign the intended worksheets before defining any ranges. Use Workbooks("Book.xlsx").Worksheets("Sheet1") for the source and Workbooks("Destination.xlsx").Worksheets("Sheet1") for the destination, changing the names as needed.

3
Calculate and write the total

Adapt and run this example: Sub WriteSumIfTotal() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim criteriaRange As Range Dim sumRange As Range Dim total As Double Dim nextRow As Long Set sourceSheet = Workbooks("Book.xlsx").Worksheets("Sheet1") Set destinationSheet = Workbooks("Destination.xlsx").Worksheets("Sheet1") Set criteriaRange = sourceSheet.Range("B2:B100") Set sumRange = sourceSheet.Range("C2:C100") total = Application.WorksheetFunction.SumIf(criteriaRange, "North", sumRange) _ + Application.WorksheetFunction.SumIf(criteriaRange, "South", sumRange) nextRow = destinationSheet.Cells(destinationSheet.Rows.Count, "B").End(xlUp).Row + 1 If nextRow < 2 Then nextRow = 2 destinationSheet.Cells(nextRow, "B").Value = total End Sub

4
Check the result

Run the macro from the VBA editor and inspect column B on the destination worksheet. If the total is still 0, check that the criteria match the source entries and that the values in the sum range are numeric.

Qualify the source ranges and destination cell in VBA
If you locate workbooks in a loop: Assign the matching workbook to an object variable and exit the loop after finding it. Check that both workbook objects were found before assigning their worksheets and ranges.
Free Microsoft Office alternative

Work with Excel-compatible spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open Excel files and continue working with spreadsheet data in a compatible environment. It does not fix VBA reference errors in Microsoft Excel; check macro compatibility before migrating VBA-dependent workbooks.

Review spreadsheet data and SUMIF formulas in a familiar interface.Open and edit Microsoft Excel formats, including XLSX and XLS.Move existing spreadsheet files into a lightweight Office suite.Use a free alternative for everyday spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why can an unqualified Range reference cause inconsistent results?

A reference such as Range("B2") can point to the active worksheet rather than the intended one. Use a worksheet object, such as sourceSheet.Range("B2"), to make the target explicit.

Do I need to activate the source workbook before calling SUMIF?

No. Assign the source worksheet to an object variable and use it to qualify both the criteria range and sum range. The macro can then calculate without Select or Activate.

How can I write the result to a specific destination sheet?

Set a destination worksheet object using Workbooks("Destination.xlsx").Worksheets("Sheet1"), then write through that object, for example destinationSheet.Range("B2").Value = total.

Why does SUMIF still return 0 after I fix the references?

Check the source criteria values for mismatches or extra spaces, and confirm that the sum-range cells contain numbers rather than text. Also verify that the criteria and sum ranges cover the intended corresponding rows.