 # 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?

Ok, now i have added a dummie of the file.
In cell G31, i want to sum all the hours posted by TW.
The formula =SUMMERHVIS(F\$8:F\$26;"a31";G\$8:G\$26), doesn't sum this for me.

## Files

• 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".

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