Sumif with Offset

Hi All,

I have a formula as below. It's a dynamic range sumif

=SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$100000)-4,1),"Actual",OFFSET(C2,0,0,COUNTA(C2:C100000)-4,1))

Column B has four tags, "Actual, Forecast, Variance, Variance %",

Then in each column after B, I have number and dollar values.

I want to add them with a dynamic range as above. Interestingly the formula works for the first column (C), but when I drag across the formula to the next columns, the number doesn't update and I get the same value as Column C total.

If I go into Column D formula:

=SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$100000)-4,1),"Actual",OFFSET(D2,0,0,COUNTA(D2:D100000)-4,1))

Make it an array then, un array it, the value updates and I get the value I want.

Why doesn't it automatically update and is there a better way to do this?

Re: Sumif with Offset

Quote from NBVC;777376

Did you check that may possibly have automatic calculation set off?

Go to Formulas tab, then click Calculation Options and make sure Automatic is checked.

Another version of the formula that should be less volatile:

=SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,COUNTA(D2:D100000)-4))

although, if your D range happens to be different than the B range, you could get errors... perhaps reference column B in both ranges for size of range.

e.g.

=SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,COUNTA($B$2:$B$100000)-4))

Display More

You're an absolute champion!

Thanks very much, your first formula gave me the same issue as my formula.

But your second formula worked a treat and is exactly what I wanted.

Thank you