CODEWITHSUNDEEP
exceldynamicexcel-formulasum
emma12345
emma12345

Reputation: 107

Dynamically update SUM range based on cell value

I have a stream of cash flow numbers as shown in picture: pic

Is there any possible way to change the sum range of G9 by changing the value of D9? For example, if I change D9's value to 5, I want the formula in G9 to automatically change to =SUM(C6:G6).

I know VBA can do this, but I don't want to use VBA since some of my colleagues don't like it. Is there any way to use some formula tricks to do it?

Upvotes: 1

Views: 4965

Answers (2)

tysonwright
tysonwright

Reputation: 1525

Cell G9 should be:

=SUM(INDIRECT("R6C3:R6C"&(2+D9),FALSE))

Upvotes: 1

user4039065
user4039065

Reputation:

try,

'volatile OFFSET method
=SUM(OFFSET(C6, 0, 0, 1, D9))
'non-volatile INDEX method
=SUM(C6:INDEX(C6:L6, 1, D9))

Upvotes: 3

Related Questions