Insert Row with Array in Formula

[Papi]

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.

 

[Dryver14]

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

 

[Aladin Akyurek]

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

 

[Papi]

Thanks for all the help.

 

[Aladin Akyurek]

Thanks for all the help.

You are welcome.