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?