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

Joachim Mund

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

Genevieve P.

Hi @Joachim Mund

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

Genevieve P.

Hi @Joachim Mund

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

Joachim Mund

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

Genevieve P.

Hi @Joachim Mund

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

Joachim Mund

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

Genevieve P.

Hi @Joachim Mund

There is a way to use a full column range and one formula to find a column within that range, however it would mean duplicating the column name into the first row of your source sheet. Then you can use an INDEX(MATCH(MATCH to have the second Match find the Column Name and use that as the reference.

However if you're adding new columns into the sheet, then you'd need to update the formula each time a new column was added to include it in your range as well, since those columns didn't exist before.

I would suggest that teaching your colleagues how to create a cross-sheet column reference each time a new column is added would be the simple way to create these updates. It's easier to update one column at a time than to learn a complex formula like INDEX(MATCH(MATCH and have to update this each time as well.