# Sum Indirect Volatile error

#### Grimm127

##### Board Regular
Hi All,

I am having trouble with the following formula. I keep getting "Volatile" when I look at the fx box.
Cell A103 = to 51

=SUM(INDIRECT("\$x\$8:\$x\$"&A103))

So my result should sum range (X8:X51) which currently has a total of 35.

Is there any way to fix this?

Thanks!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Special-K99

##### Well-known Member
What result are you getting?

#### Andrew Poulsom

##### MrExcel MVP
INDIRECT is a volatile function. For a non-volatile version:

=SUM(\$X\$8:INDEX(X:X,A103))

#### Rick Rothstein

##### MrExcel MVP
I am having trouble with the following formula. I keep getting "Volatile" when I look at the fx box.
Cell A103 = to 51

=SUM(INDIRECT("\$x\$8:\$x\$"&A103))

So my result should sum range (X8:X51) which currently has a total of 35.
This version of your formula would not be Volatile...

=SUM(INDEX(X:X,8):INDEX(X:X,A103))

#### Grimm127

##### Board Regular
This version of your formula would not be Volatile...

=SUM(INDEX(X:X,8):INDEX(X:X,A103))

#### Rick Rothstein

##### MrExcel MVP

Andrews formula would also work for you and is slightly simpler having one less function call. The difference between our formulas would show up if you decided to insert any rows before Row 8... if you did that, the \$X\$8 in Andrews formula would update to \$X\$9 where as the Index(X:X,8) in my formula (which is the equivalent to his \$X\$8) would not change... it would depend on why you inserted the row and what you planned to do next which formula you should use. If you do not plan on inserting any rows before Row 8, then I would probably use Andrew's formula instead of mine.

Replies
32
Views
569
Replies
1
Views
221
Replies
1
Views
462
Replies
3
Views
286
Replies
4
Views
391

1,191,535
Messages
5,987,157
Members
440,082
Latest member
belodelokelo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back