Sum variable number of columns in same row, but avoiding circular reference error

Hello all.

This is my first post, and I'm a newcomer to Excel!

I have tried to write a function to:

• dynamically sum (ie the sum updates as additional columns of values typed) all values rightward of a certain cell along one row - D2 in this case.
• If some of the intermediate cells along that row are blank, these need to be treated as having a value = zero

However I can't figure out how to break out of the resulting circular reference.

Here's the function in question, and the .xlsx is attached.
=SUM(OFFSET(D2,0,0,1,COUNTA(2:2)))

Hi & welcome to the board.
How about

Formula:

Please Login or Register  to view this content.

Thanks for the reply.

I was trying to make it dynamic by not giving a fixed end cell for the SUM() array, so please explain how yours works, is ZZ2 sufficiently far away?

As long as there is nothing to the right of the last cell you want to sum, you can just use a range that is large enough to cover your data. I just used ZZ as an example, but you could take it out to XFD

I also try to avoid using the offset function as it's volatile. Added to which using Counta when you have blank cells in the range, will (most likely) give wrong results.