Adicione valores em uma coluna até um certo ponto no Excel e repita

Question

Eu tenho vários milhares de valores semelhantes aos mostrados na imagem fornecida.

Eu estou tentando somar os valores de tempo na coluna K, mas somente se eles tiverem um valor de "flag" na coluna M e apenas valores consecutivos.

Por exemplo, as células M467-M477 estão todas marcadas e, quando somadas, são iguais a ~ 0,017 horas.

Então as células M478-M480 são ignoradas, então as células M481-M483 são somadas para serem iguais a ~ 0,0036 horas, e assim por diante. O que é uma fórmula de excel automatizada que fará isso?

Exemplo de valores de planilha

    

Answer 1

Uma opção com uma coluna auxiliar.

AfórmulaemQ2é

=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))

e em R2

=IF(Q3="",Q2,"")

Copie para baixo.

    

Answer 2

Se você quiser que a soma esteja na primeira linha de cada grupo, use esta fórmula:

=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")

Quebrando:

Os dois INDEX(K:K,MATCH(1E+99,K:K)+1) localizam a última célula na coluna K que tem um número e define isso como a extensão do intervalo referenciado. Dessa forma, a fórmula é dinâmica, conforme novas linhas são adicionadas, a fórmula é ajustada automaticamente.

Então, dentro da Soma, começamos com a célula que está na mesma linha, o K2 é relativo e, à medida que a fórmula se move, a referência é feita. A célula final no intervalo é definida com outro ÍNDICE / CORRESPONDÊNCIA.

Desta vez, estamos procurando pela primeira célula da linha na qual a fórmula está vazia na coluna M. Definimos essa linha -1 como a última linha para o SUM ().

O IF () simplesmente faz qualquer um que queremos fazer a soma e o resto é uma string vazia.

Sevocêquiserlocalizadonaparteinferiordogrupo,useestafórmuladematriz:

=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")

Sendo uma fórmula de matriz, ela deve ser confirmada com Ctrl-Shift-Enter ao sair do modo de edição. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Desta vez, estamos procurando o último espaço vazio em M acima da linha atual para definir o primeiro.

O IFERROR é para lidar com a linha do título, se a linha abaixo tiver "Flag".