Excel下SUMPRODUCT仅计算非空标记列匹配乘积和的实现方法

核心逻辑 #

严格匹配计算规则，整体是双数组逐位匹配求和的逻辑：

• 生成判断数组：对当前待计算的标记行，逐列判断单元格是否为非空，非空转换为数值1，空值转换为数值0，作为计算开关
• 生成数值数组：两个固定数值行（示例里的ROW5、ROW6）逐列计算同列单元格的乘积，作为每列对应的待计算值
• 借助SUMPRODUCT的数组逐乘求和特性，自动跳过标记为空的列，得到最终结果

落地公式说明 #

先对应示例表格的单元格位置（默认A列为行标签列，COL1/COL2/COL3对应B、C、D列）：

• 标记行内容范围（第1-4行的标记单元格）：B2:D5
• 固定数值行内容范围（第5、6行的数值单元格）：B6:D7

在第一行标记的结果列写入如下公式，下拉填充即可批量得到所有4个标记行的计算结果：
=SUMPRODUCT(--(B2:D2<>""), B$6:D$6*B$7:D$7)

结果核验 #

公式输出和给出的预期结果完全一致：

• 第1行标记：非空位置为COL1、COL3，计算值为1*2 + 2*3 = 8
• 第2行标记：非空位置为COL2、COL3，计算值为1.5*1 + 2*3 = 7.5
• 第3行标记：非空位置为COL1、COL2，计算值为1*2 +1.5*1 =3.5
• 第4行无任何标记，计算值为0

细节说明 #

• 公式中的B$6、B$7采用行绝对引用写法，下拉公式时，固定引用的两个数值行不会随单元格位置偏移
• 开头的--作用是把逻辑判断返回的TRUE/FALSE转换为可参与计算的1/0，也可以替换为N()函数实现同等效果，替代写法为：=SUMPRODUCT(N(B2:D2<>""), B$6:D$6*B$7:D$7)
• 公式无额外限制，只要标记单元格为非空状态（无论内容是X、文字还是数字），都会被自动纳入计算范围，空值直接跳过

内容的提问来源于stack exchange，提问作者为社区用户。