# Insert Row with Array in Formula

#### Papi

##### Well-known Member
I have the following in a number of columns using an array formula.

{=SUM(IF(Sheet1!\$C\$2:\$C\$100=\$C3,IF(Sheet1!\$G\$1:\$N\$1-DAY(Sheet1!\$G\$1:\$N\$1)+1=F\$1+0,Sheet1!\$G\$2:\$N\$100)))}

I now have to insert a row into row 3 and then attempt to have a macro copy the formula down from row 2 but it does not change the part that shows Row 100 to match the new bottom row of 101. The formula has been in place and worked fine until I had to insert a new row and I'm not clear what I will have to do, especially given that it is an array formula.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try this,

Copy down then ctrl shift enter, I have added 10 rows to give scope

=SUM(IF(Sheet1!\$C\$2:\$C\$110=\$C3,IF(Sheet1!\$G\$1:\$N\$1-DAY(Sheet1!\$G\$1:\$N\$1)+1=F\$1+0,Sheet1!\$G\$2:\$N\$110)))

I have the following in a number of columns using an array formula.

{=SUM(IF(Sheet1!\$C\$2:\$C\$100=\$C3,IF(Sheet1!\$G\$1:\$N\$1-DAY(Sheet1!\$G\$1:\$N\$1)+1=F\$1+0,Sheet1!\$G\$2:\$N\$100)))}

I now have to insert a row into row 3 and then attempt to have a macro copy the formula down from row 2 but it does not change the part that shows Row 100 to match the new bottom row of 101. The formula has been in place and worked fine until I had to insert a new row and I'm not clear what I will have to do, especially given that it is an array formula.

Assuming that the column C on Sheet1 consists of text data, let's install the following.

Define Lrow with Scope set to Sheet1 by means of Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),Sheet1!\$C:\$C)

Define Crange (or any other convenient name) with Scope set to Workbook as referring to:

=Sheet1!\$C\$1:INDEX(Sheet1!\$C:\$C,Sheet1!Lrow)

Define DataRange ((or any other convenient name) with Scope set to Workbook as referring to:

=Sheet1!\$G\$1:INDEX(Sheet1!\$N:\$N,Sheet1!Lrow)

Now invoke: Control+shift+enter, not just enter...

=SUM(IF(Crange=\$C3,IF(INDEX(DataRange,1,0)-DAY(INDEX(DataRange,1,0))+1=F\$1+0,DataRange)))

Thanks for all the help.

Replies
2
Views
95
Replies
9
Views
160
Replies
9
Views
154
Replies
0
Views
141
Replies
0
Views
45

1,203,502
Messages
6,055,777
Members
444,823
Latest member
AnAverageGuy

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