1

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?

2
  • 1
    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, 2011 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, 2011 at 12:59

2 Answers 2

1

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.

2
  • Outlining how to work around this is beyond the scope of your question – check this question for some pointers.
    – kopischke
    Oct 30, 2011 at 16:10
  • Thanks. This is the closest anyone has come to being able to answer this, including myself. =) Nov 2, 2011 at 21:29
1

You have have to make the data into a Table.

ExcelIsFun has a video about it on youtube.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .