在ARRAYFORMULA中使用含≤条件的SUMPRODUCT遇尺寸不匹配错误

问题根源 #

你使用的=Arrayformula(SUMPRODUCT(Amount,Date<=(C4:C),((Type<>&quot;A&quot;)*-1)+((Type=&quot;A&quot;)*1)))会返回单个总和值，而非和C4:C长度一致的数组，这就是尺寸不匹配报错的核心原因——SUMPRODUCT本身是聚合函数，套ARRAYFORMULA不会自动逐行计算。

可行解决方案 #

方案1：用BYROW（新版Google Sheets推荐） #

直接逐行处理每个日期，逻辑和手动公式完全一致：

=BYROW(C4:C, LAMBDA(current_date, IF(current_date="", "", SUMPRODUCT(Amount, Date<=current_date, IF(Type="A", 1, -1)))))

• 工作原理：BYROW遍历C列每一行的日期，对每个日期单独执行SUMPRODUCT，计算所有日期≤当前行日期的条目：Type A的Amount加值，Type B/C的Amount减值。
• 空行会返回空白，避免填充无效值。

方案2：用MMULT（兼容旧版Google Sheets） #

通过矩阵乘法实现逐行累加：

=ARRAYFORMULA(IF(C4:C="", "", MMULT(N(Date<=TRANSPOSE(C4:C)), Amount*IF(Type="A", 1, -1))))

• 工作原理：
  1. N(Date<=TRANSPOSE(C4:C))生成一个布尔矩阵，标记每个日期是否≤当前行日期；
  2. Amount*IF(Type="A", 1, -1)生成带正负号的金额数组；
  3. MMULT将两个数组相乘，得到每行对应的累加结果。

关键说明 #

两个方案都不受排序、筛选影响，因为计算基于命名范围的原始数据集，而非表格显示的行顺序。

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