Is it possible to change formula range, without changing the contents?

    I'm using the formula =SUMIF(F18:F18,"LY",G18:G18), =SUMIF(F18:F18,"KT",G18:G18), and so on.
    I want to change the range to:     =SUMIF(F7:F18,"LY",G7:G18), =SUMIF(F7:F18,"KT",G7:G18), and so on.

    I can do it manually, but it's 20 rows.


    Is there a way where i can change the formula range on the first and copy it down, without also copying the initials, (etc. "LY") ?



    Re: Is it possible to change formula range, without changing the contents?


    Could you stick the initials in another column (which you can hide later) and refer to them, rather than hard-coding them into the formulas individually?

    Re: Is it possible to change formula range, without changing the contents?


    Yes, i have all the initials in a column on the left, but i dont know how to refer to them with this formula. Is that possible?

    Re: Is it possible to change formula range, without changing the contents?


    If you refer to a cell with text, it'll pick up that text for your formula. So '=SUMIF(F7:F18,A1,G7:G18)' (changing A1 to where ever your data is).

    Re: Is it possible to change formula range, without changing the contents?


    Ok, I see. Instead of "LY" I put "A31", '=SUMIF(F7:F18,A31,G7:G18)'
    But when I for example put "LY" on     F8 and "9" on G8, the formula dosent match "LY" with "A31". So i do get "0" instead of "9".
    Something i do wrong?





    Re: Is it possible to change formula range, without changing the contents?


    What exactly is the formula that isn't working? Can you load a sample set of data (dummy data in the same format is fine)?

    Re: Is it possible to change formula range, without changing the contents?


    I'm sorry, i wrote the formula in wrong language!
    It should be =SUMIF(F$8:F$26;"a31";G$8:G$26).

    Re: Is it possible to change formula range, without changing the contents?


    =SUMIF(F$8:F$26;a31;G$8:G$26)
    The quote marks are making Excel try to match "a31" rather than "the value in cell a31".

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login