sumifs dynamic range problem

Joachim Mund

Hi all,

I have a sheet with a summary section in the first lines.

The summary section has sumifs formulars with a range like "budget$55:budget159" which will work fine.

If a new line 160 is inserted by a form, the formular will not be expanded to "budget$55:budget160".

Trying to change the formula to an endless end like "budget$55:budget" will not work.

"budget:budget" is not possible because this formula is in the budget row => circular reference

Is there any way to solve this?

Paul Newcome

You can now actually use once circular reference in a column. Try Using Budget:Budget in the Budget column. It can only be done once in that column, but if all you have is the single summary row, you should be good to go.

Paul Newcome

You can now actually use once circular reference in a column. Try Using Budget:Budget in the Budget column. It can only be done once in that column, but if all you have is the single summary row, you should be good to go.

Joachim Mund

Hi Paul,

thanks for this good information.

Because I have around 10 different summary lines I came to the solution to build a helper column which contents nothing but "=budget@row" thus I can use this helper column for my sumifs and fixed my problem.

thanks again for your good contributions and have a good day

Paul Newcome

Happy to help! 👍️