'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix #REF! Errors in Excel SUMPRODUCT Named Ranges
Question details
The user needs to resolve a #REF! error occurring in a SUMPRODUCT formula used to count monthly records based on named ranges.
- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- Counting records by month using the SUMPRODUCT function and custom named ranges.
- Observed behavior
- The formula returns a #REF! error because the named ranges are pointing to deleted tables, sheets, or previous trackers.
Before you begin troubleshooting, make sure you have not recently deleted or moved any source data tables or worksheets that your SUMPRODUCT formula relies on.
Recreate Invalid Named Ranges via the Name Manager
Clear out broken references from previous tables or trackers by updating your named ranges in the Name Manager tool.
When column names are reused from previous tables or an older tracker, the Name Manager may retain references to deleted sheets or ranges. This invalid reference triggers a #REF! error inside your SUMPRODUCT formula.
Navigate to the 'Formulas' tab on the Excel ribbon and click on 'Name Manager'.
Look through the list of named ranges (such as A_RCVD, S_RCVD) and check the 'Refers To' column. Identify any names that display a #REF! error.
Select the invalid named ranges and click the 'Delete' button to remove them from your workbook.
Click 'New' in the Name Manager to recreate the named ranges, ensuring they point to unique, valid cell references in your current worksheet.
Select the cell containing your SUMPRODUCT formula, press Enter to recalculate, and verify that the #REF! error is resolved.
Easily Resolve Formula Errors with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive Name Manager and comprehensive support for complex formulas like SUMPRODUCT. Fix reference errors seamlessly within a familiar, user-friendly interface.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing the #REF! error.
- 2. Access the Name Manager: Go to the 'Formulas' tab and click on 'Name Manager'.
- 3. Update named ranges: Delete any broken definitions and assign your names to the correct data ranges.
- 4. Verify your calculation: Check your SUMPRODUCT monthly count to ensure the formula now calculates correctly.
Frequently Asked Questions
What does a #REF! error mean in a SUMPRODUCT formula?
A #REF! error indicates that the SUMPRODUCT formula is trying to calculate using a cell reference or named range that is no longer valid. This usually happens when rows, columns, or sheets containing the referenced data have been deleted.
How do I find which named range is causing the #REF! error?
Go to the Formulas tab and open the Name Manager. Review the 'Refers To' column for each named range. Any entry showing '#REF!' has lost its original data source and needs to be updated or recreated.
Can I edit an existing named range instead of deleting it?
Yes, you can select the named range in the Name Manager, click Edit, and manually change the cell reference to point to the correct data. However, deleting and recreating the name is often faster if multiple ranges are corrupted from an old tracker.
