Coloque o valor de M16 na parte inferior do arquivo ou em uma célula que você não move! Na verdade, toda vez que você adiciona uma linha, automaticamente todas as referências à célula são modificadas!
Eu tenho a seguinte fórmula na célula M8 :
{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}
com esta fórmula, obtenha o total de horas por semana na coluna M de cada quarta linha olhando para M16, como m16 + m20 + m24 + m28, etc. Continuo adicionando os novos dados no topo, então quando eu abro a planilha Eu terei as informações mais atuais no topo. Quando adicionar as quatro novas linhas para meus novos dados, a fórmula se parece com:
{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}
Como faço para começar sempre a adicionar M16, porque é aí que o meu novo total de horas para a nova semana será.
Coloque o valor de M16 na parte inferior do arquivo ou em uma célula que você não move! Na verdade, toda vez que você adiciona uma linha, automaticamente todas as referências à célula são modificadas!
Primeiro, você deve alterar isso para uma fórmula SUMPRODUCT . Não requer que você o insira como uma fórmula de matriz e - IMHO - é mais fácil de usar.
=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))
Para contornar o comportamento do Excel ao inserir linhas, recomendo transformar seus dados em uma tabela. (Inserir faixa de opções > Tabela) Torna a referência "todos os dados nesta área" muito fácil e atualiza a maneira que você deseja sempre que a tabela fica maior ou menor. Nesse caso, a fórmula seria algo como isto:
=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))
Se você não puder transformá-lo em uma tabela por algum motivo, recomendo criar um intervalo nomeado e fazer referência a ele. A fórmula para o intervalo nomeado seria:
=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)
... e a fórmula referenciada seria:
=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))
... onde rngHours é qualquer nome que você forneceu o intervalo nomeado.
Se você realmente não quiser usar intervalos nomeados, pode esmagar a coisa toda em uma fórmula grande, mas ela ficará confusa, embora funcione:
=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))