Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
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 '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
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Outlining how to work around this is beyond the scope of your question – check this question for some pointers. –  kopischke Oct 30 '11 at 16:10
    
Thanks. This is the closest anyone has come to being able to answer this, including myself. =) –  Blair Holloway Nov 2 '11 at 21:29
add comment

You have have to make the data into a Table.

ExcelIsFun has a video about it on youtube.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.