Во-первых, вы должны изменить это на формулу SUMPRODUCT
. Это не требует, чтобы вы вводили его как формулу массива и - ИМХО - его проще использовать.
=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))
Чтобы обойти поведение Excel при вставке строк, я рекомендую превратить ваши данные в таблицу. (Вставьте ленту> Таблица) Это делает ссылку на "все данные в этой области" действительно простой и обновляет ее так, как вам нужно, когда таблица становится больше или меньше. В этом случае формула будет выглядеть примерно так:
=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))
Если по какой-то причине вы не можете превратить его в таблицу, я бы рекомендовал создать именованный диапазон и ссылаться на него. Формула для именованного диапазона будет иметь вид:
=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)
... и формула, ссылающаяся на это будет:
=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))
... где rngHours
- это любое имя, которое вы дали названному диапазону.
Если вы действительно не хотите использовать именованные диапазоны, вы можете объединить все это в одну большую формулу, но она получится грязной, хотя и с функцией:
=SUMPRODUCT(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)*(MOD(ROW(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15))-ROW(OFFSET(Sheet3!$M$1,15,0)),4)=0))