Sumif with Offset

  • Hi All,


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


    Code
    1. =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:


    Code
    1. =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


    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:


    [COLOR="#0000FF"]=SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,COUNTA(D2:D100000)-4))[/COLOR]


    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.
    [COLOR="#0000FF"]
    =SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,[COLOR="#FF0000"]COUNTA($B$2:$B$100000)[/COLOR]-4))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: Sumif with Offset




    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