在实际工作中，求和区域经常需要新增，这很常见，比如每个月增加一条记录。

对于这种情况，就应该将求和公式的区域写成灵活的，无论增加多少行，都能自动纳入计算区域，这样就不必每次手工调整计算区域，万一忘记就出错。

自动调整区域的求和公式之前我教过，今天的案例难度升级了，在此基础上还要考虑带条件求和。

计算下图 1 中各部门每月的业绩小计以及公司总业绩，要求如下：

由于人员经常有增减，为了方便和避免出错，希望小计和总计的计算区域能自动变化，特别是新增行的时候，能自动将它算进去；

C 列中状态不为空的人的业绩不要计算在内。

效果如下图 2 所示。

1. 在 D7 单元格中输入以下公式 --> 向右拖动，复制公式：

=SUMIF($C2:INDEX($C:$C,ROW()-1),"",D2:INDEX(D:D,ROW()-1))

公式释义：

INDEX(D:D,ROW()-1)：ROW() 是当前行所在的行号，ROW()-1 就是当前单元格上一行的行号；这个公式表示在 D 列中，定位到当前单元格上一行的单元格；

D2:...：

D2 单元格是“销售一部”的第一个人的 1 月业绩所在的单元格；如果没有其他符加条件，只需加个 sum，就是将本部门的第一个人至最后一个人的业绩相加；

到这里，已经解决了第一个需求，就是始终计算该部门的所有人，即便将来新增了人，也是如此，因为末尾那个人是活动的，永远定位到公式的上一行；这个套路公式需要记住；

SUMIF($C2:INDEX($C:$C,ROW()-1),""...)：这里用了 sumif 来判断 C 列对应区域的单元格是否为空，如果为空，则对 D 列的区域求和，不为空的不求和；这样就满足了需求中的第二个条件。

* 请注意公式中的绝对和相对引用。

2. 向右拖动，复制公式。

3. 将公式复制到其他部门的“小计”区域，记得修改两个 index 函数前面的起始单元格，须改为当前要计算区域的第一个单元格。

接下来计算总计公式。

4. 在 D20 单元格中输入以下公式 --> 向右拖动，复制公式：

=SUMPRODUCT(($A2:INDEX($A:$A,ROW()-1)="小计")*D2:INDEX(D:D,ROW()-1))

公式释义：

$A2:INDEX($A:$A,ROW()-1)：这个区域的第二个参数的设置方法前面已经讲过了，永远定位到当前单元格的上一行；

...="小计"：判断上述区域中的每个单元格内容是否为“小计”，结果为 true 或 false；

SUMPRODUCT(...*D2:INDEX(D:D,ROW()-1))：将上述逻辑值与 D 列的所有数值相乘，只有上述条件为 true 的，D 列结果才能保留下来；最后用 sumproduct 求和。