如何在Google Sheets中跨范围对公式求和计算筹款销售应缴金额

你可以用SUMPRODUCT搭配数组查询类函数直接一步算出总金额，无需逐列编写计算逻辑。

基础公式（单次编写后可下拉批量计算） #

假设你的表格结构如下：

• 销售记录表首行（第1行）从B列开始为所有产品名称，50款产品对应范围为B$1:AY$1
• 第2行开始为每名童子军对应产品的销售数量，第2行的销量范围为B2:AY2
• 价格表存放在Sheet3的A、B列，A列为商品名、B列为对应价格

在「应缴总额」列的第2行（即A2单元格）输入以下公式即可得到第一行数据的总金额：

=SUMPRODUCT(XLOOKUP(B$1:AY$1, Sheet3!A:A, Sheet3!B:B, 0) * B2:AY2)

如果你的Google Sheets版本不支持XLOOKUP，可以改用VLOOKUP的数组版本：

=SUMPRODUCT(ARRAYFORMULA(VLOOKUP(B$1:AY$1, Sheet3!A:B, 2, FALSE)) * B2:AY2)

公式逻辑说明 #

1. 查找函数会批量匹配首行所有产品的对应价格，生成和产品列数一致的价格数组
2. 价格数组和当前行的销量数组逐位相乘，得到每款产品的销售额
3. SUMPRODUCT自动将所有销售额加总，直接输出总应缴金额

使用注意 #

• 请将公式中的B$1:AY$1替换为你实际的产品名称所在的行范围，注意行号前要加$锁定，避免下拉公式时范围偏移
• 公式中B2:AY2为当前行的销量范围，不要锁定行号，下拉公式时会自动匹配下一名童子军的销量数据
• XLOOKUP的第四个参数0表示如果某款产品未录入价格表，默认按0元计算，如果需要排查未录入的产品，可以将0改为#N/A，公式会对缺失价格的产品返回错误提示

优化方案（自动匹配新增数据无需下拉） #

如果需要新增童子军数据时自动计算应缴总额，无需手动下拉公式，可以在A2单元格输入溢出公式：

=BYROW(B2:AY, LAMBDA(row, IF(COUNTA(row)=0, "", SUMPRODUCT(XLOOKUP(B$1:AY$1, Sheet3!A:A, Sheet3!B:B, 0)*row))))

该公式会自动遍历所有销量行，有数据的行自动计算总金额，空行不返回内容。

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