# #REF Error when summing inside a spill range

#### lhaggs

##### New Member
I'm trying to calculate an array of values using a function that spills its output into the cells below it. One of theses cells needs to contain a sum of the numbers above it, but the ways I've tried to do this sum have only resulted in a #REF error in the cell so far.

I've attached a picture of the sheet, and the formula that's causing this issue is =IF(A3#>0,IF(A3#<m,FCI/N,IF(A3#=m,FCI-SUM(INDIRECT(OFFSET(INDEX(A3#,1),1,5,ROW()-3,1))),)),) in cell F3. The other columns G and H which show the #REF error reference column F which is why they show the same error.

#### Attachments

• excel snip.PNG
49.1 KB · Views: 7

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part
Excel Formula:
``ROW()-3``
equates to 0 if the formula is in row3

#### lhaggs

##### New Member
Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part
Excel Formula:
``ROW()-3``
equates to 0 if the formula is in row3
Thanks for the reply! I can see why telling the OFFSET function to make a range with no height would set it off. My #REF error still persist if I change the formula to a different number than 3, and anything other than 3 results in a circular reference, but I have no idea why.

#### Fluff

##### MrExcel MVP, Moderator
Maybe because the offset is looking at col F, where the formula is.

Replies
4
Views
269
Replies
1
Views
70
Replies
9
Views
152
Replies
3
Views
177
Replies
2
Views
205

1,129,563
Messages
5,637,067
Members
416,956
Latest member
mitzhaki

### 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