SUMPRODUCT returns 0

Question

SUMPRODUCT returns 0

Gary Berndt 0

I have the following SUMPRODUCT formula: =IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&rngWorksheets&"'!"&H$26),INDIRECT("'"&rngWorksheets&"'!$L:$L"),$Q$5,

INDIRECT("'"&rngWorksheets&"'!$M:$M"),$B11)),)

It had worked as it should for years, but now it returns a value of 0. The calculation is set to Automatic; if I select Calculate Sheet the correct value is returned, but if I save the workbook the value reverts to 0.

Below are the final three steps in the Evaluate Formula process. Removing the "IFERROR" function has no effect, a value of 0 is returned. I can't understand why the correct value of 3365.5 is not returned.

Any insight would be appreciated.

Thanks.

User's image

0 comments

1 answer

Your answer

Answer 1

As far as I can tell, this might be caused by the combination of SUMIFS, INDIRECT, and an array of worksheet names inside SUMPRODUCT. Excel will often evaluate that construction incorrectly during workbook open/save recalculation and cache the result as 0 until a forced recalculation occurs.

Your formula might be working after Calculate Sheet because that command forces Excel to fully resolve the INDIRECT references. During normal recalculation, especially after reopening or saving, Excel sometimes evaluates the SUMIFS array portion before the INDIRECT references are fully resolved, so SUMPRODUCT receives an array of zeros.

Try replacing SUMIFS with SUMPRODUCT in

SUMIFS(
    INDIRECT("'"&rngWorksheets&"'!"&H$26),
    INDIRECT("'"&rngWorksheets&"'!$L:$L"),$Q$5,
    INDIRECT("'"&rngWorksheets&"'!$M:$M"),$B11
)

so it looks like this:

=SUMPRODUCT(
SUMPRODUCT(
(INDIRECT("'"&rngWorksheets&"'!$L:$L")=$Q$5)*
(INDIRECT("'"&rngWorksheets&"'!$M:$M")=$B11)*
INDIRECT("'"&rngWorksheets&"'!"&H$26)
))

If performance becomes slow, avoid full-column references inside INDIRECT. Full-column arrays combined with volatile functions are very calculation-intensive and can trigger unstable recalc behavior. Limiting the ranges might improve reliability:

=SUMPRODUCT(
SUMPRODUCT(
(INDIRECT("'"&rngWorksheets&"'!$L$1:$L$5000")=$Q$5)*
(INDIRECT("'"&rngWorksheets&"'!$M$1:$M$5000")=$B11)*
INDIRECT("'"&rngWorksheets&"'!"&H$26)
))

Another thing worth checking is whether H$26 sometimes resolves to an entire-column reference. If H$26 contains something like $N:$N, Excel can behave unpredictably when that is passed through INDIRECT into SUMIFS.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Gary Berndt 0 Reputation points

2026-06-10T21:43:56.39+00:00

Thanks, Marcin, for the suggestions.

Unfortunately, I wasn't able to get them to work. This issue is puzzling. The original formula worked superbly for 4+ years; then at some point it stopped working. I'm not aware of anything within the individual workbooks that changed to have caused the formula to stop working. I believe it actually produced an error value, which returned 0 due to the IFERROR function. I believe something about the "rngWorksheets" named range was causing the error, but I haven't been able to figure it out. I never fully understood the role of SUMPRODUCT to prevent a #SPILL! error; it seemed like it could be unstable, but it worked.

I finally gave up and replaced the "rngWorksheets" named range with a calculation (valWksheet) that returned the target worksheet name in the formula -IFERROR(SUMIFS(INDIRECT(valWksheet&"!"&H$26),INDIRECT(valWksheet&"!$L:$L"),$Q$5,INDIRECT(valWksheet&"!$M:$M"),$B8),). This new formula is shorter and IMO seems more robust.

Thanks, again.
Marcin Policht 95,355 Reputation points MVP Volunteer Moderator

2026-06-10T22:55:20.41+00:00

Hi Gary - I'm glad you managed to find an acceptable workaround

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin