# Excel doesn't automatically update ranges in SUMIF expression — why?

In Excel 2007, I have a worksheet arranged like this:

``````            A           B           C
1           x           2           15
2           x           3           45
3           x           4           46
4           x           1           7
5           x           2           85
6           x           1           14
7           x           1           9
8           x           3           36
9           x           1           5
10
11          C Total:                262
12          C Total where B > 1:    227
``````

`C11` is a regular `SUM`:

``````=SUM(C\$1:C9)
``````

`C12` is a `SUMIF` that only counts the value in column `C` if the value in `B` is greater than 1:

``````=SUMIF(B\$1:B9, ">1", C\$1:C9)
``````

Here's my problem: I insert a row at row `10`, and add additional data:

``````9           x           1           5
10          y           1           17
11
12          C Total:                265
13          C Total where B > 1:    227
``````

After entering the data at `C10`, Excel helpfully automatically updates the range in the `SUM` formula, but not the `SUMIF`:

``````=SUM(C\$1:C10)

=SUMIF(B\$1:B9, ">1", C\$1:C9)
``````

I commonly update this worksheet one row at a time, so I lose a fair amount of time having to adjust the ranges in the formula.

Is there a reason Excel doesn't automatically update the ranges in `SUMIF`, like it does with `SUM`? Can it be made to do so, or is there a workaround for the behaviour?

-
I don't recall ever seeing my SUM formulas extend automatically like you're describing... Workaround: extend your SUMIF formula to include a blank row and insert above that blank row going forward. Alternatively, use find/replace to fix the ranges in your SUMIF more easily. – variant Sep 7 '11 at 13:53
@variant Actually, it does as soon as you enter values in the newly inserted rows (tested on Excel:mac 2011). – kopischke Oct 30 '11 at 12:59

What you have observed in the case of `SUM` is Excel’s automatic formula expansion in action – the bit of magic Excel performs to adjust formula references when you add rows or columns. As `SUM` and `SUMIF` are very similar (they share the ability to restrict themselves to the part of a range filled with data, for instance – see this MSDN article), one would rightly expect both to be targeted by formula expansion. The fact `SUMIF` is not, or at least not on Office 2007 and Office:mac 2011, I would call buggy behavior (it might not technically be a bug). I’m afraid there is nothing you can do to change this.