# How to make many column formulas ignore the same lines in Excel?

I have multiple columns and at the end of each one I have a SUM and MEAN lines. Now I want to ignore some lines thus making `=SUM(A1:A15)` into `=SUM(A1:A13;A15)` if I want to ignore line 14. Doing this for each formula is a tedious task.

In my previous question I found out how to do it for a single column. I would place the `A1:A15` string in a separate cell, then reference it using `INDIRECT`. This way I can alter a single cell and update every formula for that column.

The problem is that I have multiple columns, with this approach I would have to update the `B1:B15` cell for each column. How to simplify this update process?

-
I can write an answer to this if I figure out how to superuser.com/questions/316239/… – Jader Dias Jul 28 '11 at 14:33

You can use the `OFFSET` function to shift an entire range by a certain amount. For example, if you had the text `="A1:A15"` in cell `C1`, you could get the range `B1:B15` by using the following formula:
``````=OFFSET(INDIRECT(C1), 0, 1)
For your reference, the function is defined in Excel as `=OFFSET(reference, rows, columns, [height], [width])`. To retrain the width/height of the original range, do not specify the `height` or `width` arguments. Also, note that the `rows` and `columns` arguments can be positive or negative (so you can shift both up/down and left/right).
Just another note, the `OFFSET` function works with entire ranges, so if in the previous example you entered the text `="A1:A12,A15"` the returned range after using `OFFSET` to shift it right one column would be `B2:B12,B15` as you would expect.
I used `OFFSET(INDIRECT(C1), 0, COLS(A16)-1)` – Jader Dias Jul 28 '11 at 16:50