Apple Number: How to dynamically refer to cells?

Question

Apple Number: How to dynamically refer to cells?

Hello dear Apple Numbers community,

I really stumbled into a newbie issue it seems: I would like to refer dynamically to cells / arrays in a function, but Numbers doesn't seem to like how I do it.

SUMIFS("A$53:"&ADDRESS(53;SPALTE()−1;2)...

Here I'm trying to compose an array reference for the SUMIFS function: from A$53 till the previous column this function is located. It seems the function just considers the first cell A$53, disregarding the rest.

I've also tried the CONCATENATE function but to no avail.

Would you have any pointers for me? Thank you so much in advance.

Posted on Feb 8, 2025 1:32 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,366 points

Feb 8, 2025 4:15 PM in response to Fallschirmjaeger

If the formula is in column C,

SUMIFS($A53::B53,......)

You can fill that left to column B and fill right to the last column

$A53 is anchored to that column A. B53 will change depending on which column the formula is in.

It will SUMIFS the cells on row 53 from column A though to the column before the formula (all on row 53)

Fill down to other rows if you want the same formula for row 54, 55, etc.

This formula will not work in column A but there is no "previous column" in column A so the formula shouldn't be in that column anyway.

Reply

Answer 2

Feb 10, 2025 11:33 AM in response to Yellowbox

Hi Yellowbox,

Thanks for your message. Yes, my bad, that function is simply OFFSET in the English language.

Unfortunately I couldn't manage to change the language on the Numbers function list online: I ended up using the Safari translation from the German name of that function, that is BEREICH.VERSCHIEBEN

It seems I'm not able to edit my previous message past a certain time. So hope this is enough to correct it. Amended, the code I used is then:

SUMIFS(OFFSET($A$54;0;0;1;COLUMN()−2); ... )
+
SUMIFS(OFFSET(A$56;0;0;1;COLUMNS($1:$1)−COLUMN()−2); ... )

Where in the first SUMIFS I count all values from column A up till two columns left of the invoking cell and in the second SUMIFS I count all values from 2 columns to the right of the invoking cell till the rightmost column of the table.

Again as explained in my previous post, the "-2" step is caused by the fact that I keep columns with data distanced by 1 empty column in the spreadsheet interface I've put together.

Here again for any further clarifications. Hope this helps!

Reply

Answer 3

Feb 9, 2025 11:26 PM in response to 6x6

Hello and thanks a lot to both of you!

I want to close this loop and also help any other person that may struggle with this particular need.

I managed to accomplish what I wanted by using this structure:

SUMIFS(RANGE.SHIFT($A$54;0;0;1;COLUMN()−1); ... )
+
SUMIFS(RANGE.SHIFT(A$56;0;0;1;COLUMNS($1:$1)−COLUMN()−2); ... )

where I basically count all values of a specific row to the left and to the right of the column this function lies onto.

You'll see that there's a 2 column step between the two SUMIFS as well as a -2 reference in the second one, as I keep relevant columns distanced by 1 empty column in the spreadsheet interface I've put together.

Hope this helps other struggling folks. Thank you both again for your help. Appreciated!

Reply