Tenho vários milhares de valores semelhantes aos mostrados na imagem fornecida.
Estou tentando somar os valores de tempo na coluna K, mas somente se eles tiverem um valor de "sinalizador" na coluna M e apenas valores consecutivos.
Por exemplo, as células M467-M477 estão todas sinalizadas e, quando somadas, equivaleriam a aproximadamente 0,017 horas.
Em seguida, as células M478-M480 são ignoradas, as células M481-M483 são somadas para equivaler a ~0,0036 horas e assim por diante. O que é uma fórmula automatizada do Excel que fará isso?
Exemplo de valores de planilha
Responder1
Uma opção com uma coluna auxiliar.
A fórmula em Q2 é
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
e em R2
=IF(Q3="",Q2,"")
Anote.
Responder2
Se quiser que a soma fique na primeira linha de cada grupo, você pode usar 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)),"")
Dividindo:
Os dois INDEX(K:K,MATCH(1E+99,K:K)+1)encontram a última célula da coluna K que possui um número e definem-na como a extensão do intervalo referenciado. Dessa forma, a fórmula é dinâmica, à medida que novas linhas são adicionadas, a fórmula se ajusta automaticamente.
Então dentro da Soma começamos com a célula que está na mesma linha, o K2 é relativo e conforme a fórmula desce o mesmo acontece com a referência. A célula final do intervalo é definida com outro INDEX/MATCH.
Desta vez, procuramos que a primeira célula da linha em que a fórmula está vazia na coluna M. Definimos essa linha -1 como a última linha para SUM().
O IF() apenas faz com que queiramos fazer a soma e o resto seja uma string vazia.
Se você quiser que ele esteja localizado na parte inferior do grupo, use esta fórmula de matriz:
=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, deve ser confirmada com Ctrl-Shift-Enter ao sair do modo de edição. Se feito corretamente, o Excel irá {}contornar a fórmula.
Desta vez procuramos 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 logo abaixo possuir “Flag”.
