SUMIF using Variable Columns

  • Hoping someone can help because I've been trying to figure this out for days!


    I am looking to sum the cells only up to the column of the selected month: O12.
    O12 is a drop down of all 12 months.


    In the example below, the selected month is Apr-15 so in cell AZ20 I will need the sum of O20:Q20, columns prior to Apr-15.
    If the month changes to May-15, I will need the sum of O20:R20 to sum in cell AZ20.



    Thank you in advance for your help!



    [ATTACH=CONFIG]71229[/ATTACH]

  • Re: SUMIF using Variable Columns


    Try:


    [COLOR="#0000FF"]=SUM($O20:INDEX($O20:$S20,MATCH($O$12,$O$19:$S$19)))[/COLOR]


    arrange the O:S range to match the first to last column of potential entries to sum in your table

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

  • Re: SUMIF using Variable Columns


    Thank you. This formula is not allowing the revision of the sum range when I change the month in O12.


    i.e
    If I change the month to Mar-15 I need to sum cells O20:P20.
    If I change the month to May-15 the sum range will change to O20:R20.


    I am looking to sum the cells that fall BEFORE the month chosen in O12.

  • Re: SUMIF using Variable Columns


    :) Off by 1 error


    Just add -1 after the $O$12 reference:
    =SUM($O20:INDEX($O20:$S20,MATCH($O$12-1,$O$19:$S$19)))

  • Re: SUMIF using Variable Columns


    Quote from richadj4;784735

    :) Off by 1 error


    Just add -1 after the $O$12 reference:
    =SUM($O20:INDEX($O20:$S20,MATCH($O$12-1,$O$19:$S$19)))


    Worked!! Thanks a million!