How to Find the Source of a #REF! Error in an Excel SUM Formula
Question details
The user needs to locate the exact cell causing a #REF! error inside a wide SUM formula range, which is often difficult to spot due to hidden columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- A SUM formula spanning multiple columns is returning a #REF! error, but the specific cell causing the error is not immediately visible.
- Observed behavior
- The SUM formula returns a #REF! error because one or more individual cells within the referenced range evaluate to a reference error.
Before troubleshooting, unhide any hidden rows or columns within your formula's range to ensure all referenced cells are fully visible and accessible.
Use 'Go To Special' to Highlight Error Cells
The fastest way to locate hidden error cells in a large dataset is by using the Go To Special feature to automatically highlight formulas that result in errors.
Highlight the entire cell range referenced in your SUM formula (for example, B42:AT42).
Press F5 or Ctrl+G on your keyboard to open the Go To dialog box, then click the 'Special...' button at the bottom.
In the Go To Special menu, select 'Formulas'. Uncheck the boxes for Numbers, Text, and Logicals, leaving only 'Errors' checked. Click OK.
Excel will now highlight all cells containing #REF! or other errors. You can now correct the broken references or delete the values so your SUM formula calculates properly.
Identify Error Cells Using a Helper Formula
If you want a dynamic, readable list of cell addresses containing errors, you can use a combination of TEXTJOIN, IF, and ISERROR functions.
Fix Spreadsheets Easily with WPS Office
WPS Spreadsheet provides powerful formula auditing and error-checking tools, allowing you to instantly spot #REF! errors, trace precedents, and evaluate complex SUM functions.
- 1. Open your spreadsheet: Launch WPS Office and open the file containing the problematic SUM formula.
- 2. Highlight the data: Select the range of cells that the failing SUM formula is trying to calculate.
- 3. Access Go To Special: Navigate to the Home tab, click on 'Find and Replace', and select 'Go To'. Click the 'Special' button.
- 4. Find the errors: Choose 'Formulas', ensure only 'Errors' is checked, and click OK to instantly highlight the #REF! cells.
Frequently Asked Questions
Why does my SUM formula result in a #REF! error?
A SUM formula returns a #REF! error if one or more individual cells within its referenced range evaluate to a #REF! error. This typically happens when cells, rows, or columns referenced by intermediate formulas get deleted.
Can I sum a range while ignoring #REF! errors?
Yes. Instead of using the SUM function, you can use the AGGREGATE function. For example, using =AGGREGATE(9, 6, B42:AT42) will calculate the sum (9) while ignoring all error values (6) in the specified range.
Does unhiding columns automatically fix the #REF! error?
No. Unhiding columns only makes the problematic cells visible so you can find them. You still need to manually correct the broken formulas or delete the #REF! values for the SUM formula to calculate properly.
