使用Sumproduct计算仅标记首行的各包裹对应物品总重量

包裹总重量计算方案 #

适用场景 #

• 包裹号仅填写在每个包裹的第一行，同包裹其余行包裹号列为空
• 不同包裹的物品行数不固定
• 需在包裹号所在行输出该包裹全部物品的总重量，总重量计算规则为所有物品的QUANTITY*WEIGHT之和

公式使用方法 #

假设你的表格结构如下（数据从第2行开始，第1行为表头）：

• A列：包裹号
• C列：QUANTITY（物品数量）
• D列：WEIGHT（物品单重）
• E列：TOTAL WEIGHT（待输出总重量）

你只需要在E2单元格输入以下公式，之后向下整列填充即可：

=IF(A2<>"",SUMPRODUCT(
  INDIRECT("C"&ROW()&":C"&IFERROR(MATCH(TRUE,INDEX(A3:A<>"",0),0)+ROW()-1,COUNTA(C:C))),
  INDIRECT("D"&ROW()&":D"&IFERROR(MATCH(TRUE,INDEX(A3:A<>"",0),0)+ROW()-1,COUNTA(D:D)))
),"")

公式逻辑说明 #

• 外层IF(A2<>"", ... ,"")：仅当前行存在包裹号时执行计算，否则返回空值
• MATCH(TRUE,INDEX(A3:A<>"",0),0)：从当前行的下一行开始查找下一个有包裹号的位置，得到两个包裹号之间的间隔行数
• IFERROR(..., COUNTA(C:C))：如果当前是最后一个包裹，没有下一个包裹号，就自动取数据最后一行作为计算结束边界
• SUMPRODUCT会对当前包裹范围内的数量、单重列逐行相乘后求和，直接得到总重量

注：该公式兼容Google Sheets和Excel 2019及以上版本，Excel 365版本可使用XLOOKUP进一步简化逻辑。

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