如何编写Excel反向求和公式：直至遇到B列有值单元格时停止

需求明确 #

当B列某行存在值时，在D列对应行计算上一个B列非空单元格的下一行到当前行的上一行的C列数据总和（即向上累加至前一个B列非空单元格为止）。

方案1：Excel 365/2021（支持动态数组） #

在D列第一个可能出现B列非空值的单元格（比如D2）输入以下公式，下拉填充即可：

=IF(B2<>"",SUM(INDEX(C:C,IFERROR(XLOOKUP(TRUE,B$1:B1<>"",ROW(B$1:B1),,-1)+1,1)):C1),"")

公式逻辑拆解 #

• IF(B2<>"", ... , "")：仅当当前行B列有值时执行计算，否则D列留空
• XLOOKUP(TRUE,B$1:B1<>"",ROW(B$1:B1),,-1)：从当前行上方区域向上查找最近的B列非空单元格的行号；若上方无任何非空单元格，返回错误值
• IFERROR(...,1)：捕获上方无B列非空单元格的情况，默认从第1行开始求和
• INDEX(C:C, ...)+1：定位到前一个B列非空单元格的下一行，作为求和区间的起始点
• SUM(...:C1)：对起始点到当前行的上一行的C列数据求和

方案2：旧版Excel（不支持XLOOKUP） #

使用数组公式，在D列对应单元格输入后按 Ctrl+Shift+Enter 确认，再下拉填充：

=IF(B2<>"",SUM(INDEX(C:C,MAX(IF(B$1:B1<>"",ROW(B$1:B1),0))+1):C1),"")

公式逻辑拆解 #

• IF(B$1:B1<>"",ROW(B$1:B1),0)：将上方B列非空行的行号保留，空行返回0
• MAX(...)：提取上方最近的B列非空单元格的行号；若无则返回0
• INDEX(C:C, ...)+1：定位求和区间的起始点（前一个非空行的下一行）
• SUM(...:C1)：计算区间内C列数据的总和

示例验证 #

假设数据如下：

公式会自动识别B列的非空单元格，计算对应区间的C列总和。

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