Fix SUMIFS #VALUE! Error with Dynamic Arrays Inside LET in Excel
Question details
Users experience a #VALUE! error when passing an inline array or a dynamic array like RANDARRAY as a range argument into SUMIFS within a LET function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing complex spreadsheet formulas that combine the LET function with SUMIFS and dynamic arrays to calculate conditions in memory.
- Observed behavior
- SUMIFS returns a #VALUE! error when referencing inline arrays directly. However, referencing a physical spilled range (e.g., A2#) works correctly.
Ensure your spreadsheet software is updated to a version that supports dynamic arrays and the LET function. Double-check your formula syntax for any misplaced parentheses before proceeding.
Substitute SUMIFS with SUMPRODUCT
Use SUMPRODUCT instead of SUMIFS, as it natively handles in-memory and dynamic arrays without requiring physical cell ranges.
Functions like SUMIFS, COUNTIFS, and AVERAGEIFS are optimized to work strictly with physical cell ranges. When you feed them an inline array or an array generated by RANDARRAY inside a LET function, they fail and return a #VALUE! error.
The SUMPRODUCT function does not have this limitation. By multiplying boolean criteria arrays with your sum array, you can achieve the exact same conditional summing logic entirely in memory.
Double-click the cell containing your LET formula to enter Edit mode.
Locate the SUMIFS segment nested inside your LET function and delete it.
Replace it with SUMPRODUCT, converting your criteria into array multiplications. For example, instead of SUMIFS(SumRange, CriteriaRange, "Value"), write SUMPRODUCT((CriteriaRange="Value")*SumRange).
Press Enter to execute the formula. SUMPRODUCT will evaluate the dynamic array correctly and output the expected sum without the #VALUE! error.
Output to a Physical Spilled Range
Bypass the error by generating the dynamic array in a helper cell first, then referencing it as a spilled range in your SUMIFS formula.
Open a Microsoft Support Request
Report this known evaluation behavior to Microsoft Support so the product team can investigate the compatibility issue.
Try WPS Office for Smooth Formula Calculation
If you are tired of running into unexpected formula evaluation errors and compatibility bugs, WPS Office provides a robust, reliable, and lightweight alternative. Experience seamless data analysis, advanced formula support, and a highly familiar interface without expensive subscriptions.
- 1. Download and Install: Visit the official WPS website, download the free installer, and complete the installation process.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and click 'Open' to load your existing .xlsx file containing the dynamic arrays.
- 3. Calculate Seamlessly: Continue editing and calculating your complex formulas with high precision and cross-compatibility.
Frequently Asked Questions
Why does SUMIFS require a physical range instead of an array?
Functions in the 'IFS' family (SUMIFS, COUNTIFS, AVERAGEIFS) were historically designed to evaluate physical cell ranges to optimize calculation speed and memory usage. As a result, they cannot natively parse in-memory arrays like {1,2,3} or those generated dynamically within the LET function.
Are spilled range references compatible with all spreadsheet functions?
Most modern functions support spilled ranges natively using the hashtag operator (e.g., A2#). Because a spilled array occupies physical grid space, even older range-bound functions like SUMIFS can process them correctly, unlike strictly in-memory array constants.
What is the primary use of the LET function in formulas?
The LET function allows you to assign names to calculation results or values within a formula. It significantly improves formula performance and readability by storing intermediate calculations, meaning Excel doesn't have to calculate the same expression multiple times in a single cell.
Can I use the FILTER function instead of SUMIFS?
Yes. Wrapping a SUM function around a FILTER function (e.g., SUM(FILTER(SumRange, CriteriaRange="Value"))) is another excellent workaround. Like SUMPRODUCT, the FILTER function can handle in-memory and dynamic arrays effortlessly.
