# Saving calculation time on large workbook

#### thenextguy

##### Board Regular
I have a list of items and costs that is sliced and diced in numerous ways using sumproduct functions.

The list is updated each year and I'd prefer not to update my formulas each year, so I've tried to make the formulas dynamic. I'm using a COUNTA (example: =COUNTA(Sheet1!B1:B65536)) formula to keep track of the number of entries on the list and then using OFFSET formulas to adjust my ranges based on this numbers.

Okay, that's the setup. This question is this:

Is there an advantage of having only one COUNTA formula and then having my OFFSET formulas reference this cell. Example:

Code:
``=SUM(OFFSET(Sheet1!A1,0,0,B1))``
where A1 contains the aforementioned COUNTA formula.

Or should I just go ahead and build the COUNTA formula into OFFSET formula. Example:

Code:
``=SUM(OFFSET(COUNTA(Sheet1!B1:B65536),0,0,B1))``

I hope this makes sense. I'll be happy to try again if anything is unclear. Also, if there is a better way to do what I'm currently trying to do, I'm all for that, too! ### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

##### MrExcel MVP
offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent

#### thenextguy

##### Board Regular
offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent

Thanks for the reply, PaddyD. Can you explain a little further? I've only used index() to refer to one item in a range. How can I use it to resize a range?

##### MrExcel MVP
as per the excel help file entry, index() can return a reference as well as a value.

if a1:a5 is populated by, e.g. row(), then

=sum(a1:index(a:a, count(a:a)))

would return 15 - or, if you had the count in b1:

=sum(a1:index(a:a, b1))

#### thenextguy

##### Board Regular
That works great, thank you for your help.

Replies
13
Views
154
Replies
9
Views
179
Replies
0
Views
55
Replies
2
Views
669
Replies
0
Views
14