Excel SUMPRODUCT隔4列双条件计数组合公式报错问题

需求说明 #

• 从B列开始，每隔4列统计非空单元格数量
• 从D列开始，每隔4列统计空单元格数量
• 最终统计**同时满足「订单标签非空且对应金额标签为空」**的单元格组合数量
• 注：各标签列之间有1个空白列分隔；单个条件公式可正常运行，但组合后出现#N/A或#VALUE!错误；实际数据列更多，但隔列规则一致

单个条件可用公式 #

非空计数（订单列） #

=SUMPRODUCT(--((MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0)*NOT(ISBLANK($B$2:$H$3))))

空值计数（金额列） #

=SUMPRODUCT(--((MOD(COLUMN($D$2:$H$3)-COLUMN(D2),4)=0)*(ISBLANK($D$2:$H$3))))

组合报错的公式 #

=SUMPRODUCT(
--((MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0)*NOT(ISBLANK($B$2:$H$3)))
*--((MOD(COLUMN($D$2:$H$3)-COLUMN(D2),4)=0)*(ISBLANK($D$2:$H$3)))
)

报错原因 #

两个条件引用的单元格区域维度不一致：$B$2:$H$3是2行×7列的数组，$D$2:$H$3是2行×5列的数组，SUMPRODUCT无法对不同维度的数组进行运算，因此返回错误。

解决方案 #

通用公式（适配任意多列） #

=SUMPRODUCT(
  --((MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0)*NOT(ISBLANK($B$2:$H$3))*ISBLANK(OFFSET($B$2:$H$3,,2)))
)

公式说明 #

1. MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0：定位从B列开始每隔4列的订单列
2. NOT(ISBLANK($B$2:$H$3))：判断订单列单元格非空
3. ISBLANK(OFFSET($B$2:$H$3,,2))：判断当前订单列右侧第2列（对应金额列）的单元格为空
4. 三个条件相乘后，SUMPRODUCT自动求和所有满足条件的组合数量

固定列数写法（示例场景） #

如果列数固定，也可以直接对每一组列对进行判断：

=SUMPRODUCT(--(NOT(ISBLANK($B$2:$B$3))*ISBLANK($D$2:$D$3)) + --(NOT(ISBLANK($F$2:$F$3))*ISBLANK($H$2:$H$3)))

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