# sum values of a column in another sheet based on the column position

Options
✭✭✭✭✭✭

Hi all,

Is there any way to sum a certain column in another sheet based on the column position?

I think of having a reference like {another sheet range} with a range of e.g. 4 columns.

I would like to sum the values of the second column, but do not know how to make this.

SUM(INDEX({another sheet range}, 2)) will not work, because it returns a cell and not a column, isn't it?

Thank you, Joachim

Tags:

Options

Thank you for clarifying! In this instance, yes, I would suggest creating a single column reference for each column, as you noted. You'll only need to create the reference once to be able to copy/paste the {text in these} and use that same column in a new formula.

Cheers,

Genevieve

Options

What if you had a top row in this source sheet that has the SUM for each column? Then you could use an INDEX Function to just bring back the top cell that already contains the SUM:

=INDEX({another sheet range}, 1, 2)

Note that in an INDEX function, it's the third part of the formula that indicates the Column. The number immediately after the range is the row, which is why I put 1 (for the top row).

Let me know if this would work for you!

Cheers,

Genevieve

• ✭✭✭✭✭✭
Options

Thank you @Genevieve P. ,

this would work as you described, but for my needs it will not do the job.

In the second KPI sheet I use sumif() and avg(collect()) this means I need the whole column nr x from the {another sheet range}. Otherwise I have to define a lot of references for each column.

Best wishes, Joachim

Options

Thank you for clarifying! In this instance, yes, I would suggest creating a single column reference for each column, as you noted. You'll only need to create the reference once to be able to copy/paste the {text in these} and use that same column in a new formula.

Cheers,

Genevieve

• ✭✭✭✭✭✭
Options

Thank you @Genevieve P. ,

yes I will do that. Unfortunately I have more than 15 different columns and each month there will be 1-2 more and I have to instruct my colleagues how to create new references.

I tried to find a way to copy these formulas without changing the reference and only take a new index.

Best wishes, Joachim