Tengo varios miles de valores similares a los que se muestran en la imagen proporcionada.
Estoy intentando sumar los valores de tiempo en la columna K, pero solo si tienen un valor de "bandera" en la columna M, y solo valores consecutivos.
Por ejemplo, todas las celdas M467-M477 están marcadas y, cuando se suman, equivaldrían a aproximadamente 0,017 horas.
Luego, se ignoran las celdas M478-M480, luego se suman las celdas M481-M483 para obtener aproximadamente 0,0036 horas, y así sucesivamente. ¿Qué es una fórmula de Excel automatizada que hará esto?
Ejemplo de valores de hoja de cálculo
Respuesta1
Una opción con una columna auxiliar.
La fórmula en Q2 es
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
y en R2
=IF(Q3="",Q2,"")
Copia.
Respuesta2
Si quieres que la suma quede en la primera fila de cada grupo puedes 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)),"")
Desglosándolo:
Los dos INDEX(K:K,MATCH(1E+99,K:K)+1)encuentran la última celda en la columna K que tiene un número y lo establecen como la extensión del rango al que se hace referencia. De esta manera, la fórmula es dinámica, a medida que se agregan nuevas filas, la fórmula se ajustará automáticamente.
Luego dentro de la Suma comenzamos con la celda que está en la misma fila, el K2 es relativo y a medida que la fórmula va bajando también lo hace la referencia. La celda final del rango se establece con otro ÍNDICE/COINCIDENCIA.
Esta vez estamos buscando que la primera celda de la fila en la que se encuentra la fórmula esté vacía en la columna M. Configuramos esa fila -1 como la última fila para SUM().
IF() simplemente hace que lo que queramos hacer la suma y el resto sea una cadena vacía.
Si desea que se ubique en la parte inferior del grupo, utilice esta fórmula matricial:
=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")
Al ser una fórmula matricial, se debe confirmar con Ctrl-Shift-Enter al salir del modo de edición. Si se hace correctamente, Excel colocará {}la fórmula.
Esta vez buscamos el último espacio vacío en M encima de la fila actual para configurar el primero.
El IFERROR es para tratar con la fila del título, si la fila justo debajo tiene "Bandera".
