Using Offset for creating a Dynamic Range

[jagostinho]

Hello,

I am trying to create a dynamic range using OFFSET and COUNTA but for some reason the sum does not add up.

Please see attached.

The 20 results from a direct SUM but I wanted to be able to update the range dynamically as the row expands with new values towards the right.

Does this make sense?

Many thanks for your help.
J.

[DBY]

Hello J welcome to the forum.

Amend you Offset formula to something like:

Please Login or Register  to view this content.

This will sum data entered until column Z but can be expanded to whatever you require to cover the data potentially being entered.

DBY

[jagostinho]

Thank DBY.

This is better than what I had, certainly.

But would there be a way so that it could update itself regardless on the number of columns?


In other words, why is COUNTA in my formula not working, then?

Thank you

[jagostinho]

got it.

this one works.

=SUM(OFFSET($B2,0,0,1,COUNTA(B2:ZA2)))

I missed the 1 for [height]
using 0 killed it.

Many thanks,

[DBY]

Hi
Because you've limited the COUNTA to column E: ,COUNTA(B2:E2). I won't count any values beyond that, also you had it in the Height argument, it is the Width when dealing with columns.

* Just seen your last post, yes you've got it.