Dynamic range with offset (skipping lines) in Excel

0
votes

I am looking for some help constructing a dynamic range formula that can skip rows.

=SUM(OFFSET(H111;5;0;COUNTA(H111:H116) + 1;1))

My idea was to start at H111 and move down 5 rows, use COUNTA to count all non-blank rows and add 1 to adjust for one blank title/colored row in between.

What I want to do is, is to be able to SUM all the accumulated amortizations for every asset in the total amortization cost field and to be able to add new items which the formula dynamically responds to.

Help is very much appreciated. Thank you!

enter image description here

With added tax rate enter image description here

1
excelexcel-formula

1 Answers

1
votes

Maybe:

=SUMIF($A$106:$A$10000,"Accumulated amortization",$H$106:$H$10000)

You may need to adjust the 10000 to reflect the lowest possible row, but the range can be significantly larger than the used range.

One problem with OFFSET is that it is volatile and, depending on the complexity of your entire worksheet, may add significantly to execution times.

Another issue with OFFSET is that, even if you do use it to generate an array of results, it requires further manipulation before you can effectively use those results in formulas.