How to dynamically adjust a range in a calculation in Excel

Question

I have an excel formula =SUM(AZ2:AZ300)

I want to make this more dynamic by using the formula =ROW(OFFSET($B$1,COUNTA($B:$B)-1,0)) in cell A1

Then I want my formula to do the following =SUM(AZ2:AZ&A1) This formula doesn't work, but it to take the sum of cells AZ2 through AZ and the number given in cell A1

For example if A1 value is 250 then I want the formula to be equivalent to =SUM(AZ2:AZ250)

Thanks in advance for your help.

Darren Bartrup-Cook · Accepted Answer · 2020-07-27 14:59:19Z

2

Rather than putting the row number in a separate cell you could combine it in the formula:
=SUM($A$2:INDEX($A:$A,COUNTA($B:$B)-1))

This does the same as the OFFSET version, with the bonus of not being volatile.

answered Jul 27, 2020 at 14:59

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to dynamically adjust a range in a calculation in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related