Google Sheets中SUMPRODUCT范围不匹配错误修复求助

问题情况 #

我在Google表格里维护学生成绩数据，要只统计筛选后显示的行里，各个班级的及格（PASS）或不及格学生人数。用的是下面这个在Excel里能正常运行的公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B$9,ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9)),,1)),($C$2:$C$9=G$4)*($D$2:$D$9="PASS"))

但放到Google表格里就报错：

SUMPRODUCT has mismatched range sizes. Expected row count: 1, column count: 1. Actual row count: 8, column count: 1.

修复办法 #

Google Sheets的SUMPRODUCT对数组的处理逻辑和Excel不一样，得调整SUBTOTAL的返回形式，下面两种方法都可行：

方法1：给SUBTOTAL套上ARRAYFORMULA #

把公式修改为：

=SUMPRODUCT(ARRAYFORMULA(SUBTOTAL(3,OFFSET($B$2:$B$9,ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9)),,1))),($C$2:$C$9=G$4)*($D$2:$D$9="PASS"))

Google Sheets里SUBTOTAL默认只会输出一个汇总值，加了ARRAYFORMULA后，它会逐行计算每行的可见状态（可见返回1，隐藏返回0），生成和后面条件数组大小匹配的8行1列数组，这样SUMPRODUCT就能正常匹配计算了。

方法2：用BYROW替代OFFSET+ROW组合（更简洁） #

利用Google Sheets原生的BYROW函数直接遍历每行判断可见性，公式更清爽：

=SUMPRODUCT(BYROW($B$2:$B$9,LAMBDA(r,SUBTOTAL(3,r))),($C$2:$C$9=G$4)*($D$2:$D$9="PASS"))

BYROW会对B2:B9的每一行执行SUBTOTAL(3,r)，判断该行是否可见并生成对应的0/1数组，再和班级、及格条件的数组相乘求和，结果就是筛选后符合条件的人数。

额外说明 #

如果要统计不及格人数，只需要把公式里的"PASS"换成对应的不及格标识（比如"FAIL"）即可。

内容的提问来源于stack exchange，提问作者Amlan Ranjan Dutta