使用数组公式时SUM函数返回#VALUE!错误及行差值求和需求

先明确你的问题场景：

• 工作表A1:G10的规律：第n行（n从1到10）的7个单元格值都是n
• J1:K10存储行号对（比如1&10、2&9这类对应行）
• 需求：对每一组行号对，计算两行对应单元格值的差的总和，同时解决之前用SUM数组公式时出现的#VALUE!错误

为什么SUM数组公式会出#VALUE!错误？ #

大概率是你没处理好数组运算的维度匹配，或是没按数组公式规则输入（旧版Excel需要按Ctrl+Shift+Enter确认，而非直接回车）。比如直接写SUM(A1:G1 - A10:G10)却没按数组公式方式提交，或是引用的行对区域和数据区域维度不兼容，都会触发这个错误。

解决方案：用SUMPRODUCT或规范的数组公式 #

方法1：计算单个行对的差值总和（比如J1:K1的1和10） #

如果要单独计算某一组行对的结果，正确的数组公式写法是：

=SUM((INDEX(A:A,J1):INDEX(G:G,J1)) - (INDEX(A:A,K1):INDEX(G:G,K1)))

• 旧版Excel输入后需按Ctrl+Shift+Enter确认；Excel 365/2021直接回车即可。
• 这个公式用INDEX精准定位行对对应的行区域，确保维度完全匹配，从根源避免#VALUE!错误。比如行1和行10的结果会是7*(1-10) = -63，符合预期。

方法2：批量计算所有行对的结果并汇总 #

如果要一次性算出J1:K10所有行对的差值总和，用SUMPRODUCT更高效（无需数组公式确认）：

=SUMPRODUCT((INDEX(A1:G10,J1:J10,ROW(A1:G1)) - INDEX(A1:G10,K1:K10,ROW(A1:G1))))

结合你的数据规律（每行值都是行号），还能简化成更简洁的写法：

=SUMPRODUCT((J1:J10 - K1:K10)*7)

因为每行有7个相同的数，差值总和直接等于(行号x - 行号y)*7，批量计算所有行对的这个值再求和，既高效又不会出错。

验证示例 #

假设J1:K10是(1,10),(2,9),(3,8),(4,7),(5,6),(6,5),(7,4),(8,3),(9,2),(10,1)，总和会是：
7*[(1-10)+(2-9)+(3-8)+(4-7)+(5-6)+(6-5)+(7-4)+(8-3)+(9-2)+(10-1)] = 7*0 = 0，完全符合逻辑。

内容的提问来源于stack exchange，提问作者Evgeny Gorb