Estoy intentando crear una hoja de cálculo para calcular la suma de las horas de cada mes en función de las filas respectivas. Para que el ejemplo sea un poco más claro, para Excel a continuación, debería obtener el valor de 28 para el Cliente 1 y 16 para el Cliente 3.
Estoy intentando utilizar la función SUMAR.SI, pero el rango de suma no puede estar en varias celdas.
Y así es como me gustaría ver la información:
Respuesta1
Puedes crear una fórmula simple:
=SUM(IF(B5:B21="Client 1",G5:AK21))
Eso es hacer un cálculo de matriz con 2 pasos:
- Devuelve FILAS COMPLETAS de números (incluso espacios en blanco) si la columna B = "Cliente 1".
- Una vez que tenga todas las filas (de las cuales la mayoría contendrá espacios en blanco), simplemente suma esos valores.
Puede reemplazar "Client 1"en la fórmula con una referencia a una celda externa si lo desea. Además, asegúrese de que todo el rango del mes sea exacto en la segunda parte de la fórmula G5:AK21.
Respuesta2
Aprovechando el excelente =SUM(IF())enfoque de@davees posible utilizar elOFFSETFunción para seleccionar las columnas correspondientes a cada uno de los 12 meses del año.
La OFFSETfunción define un rango rectangular a través de su altura y ancho, con la esquina superior izquierda del rango definida en términos del número de filas debajo y columnas a la derecha de una celda de la hoja de trabajo especificada. En el siguiente método, utilizo la celda resaltada en amarillo como celda especificada (celda E17).
El rango A2:E14(ver captura de pantalla a continuación) proporciona cierta información, ometadatos, sobre dónde se ubican las columnas de valores diarios para cada uno de los 12 meses del año, en relación con la celda resaltada. Los valores útiles están en las columnas encabezadas.Col_Offsetycolumnasasí, por ejemplo, para enero los valores diarios comienzan en la primera columna a la derecha de la celda amarilla y ocupan 31 columnas, para febrero los valores diarios comienzan en la columna 32 a la derecha de la celda amarilla y ocupan 29 columnas (cambiando el año a un año no bisiesto en la celda B1cambiaría el valor en la celda E4a 28), etc. Las columnas encabezadasMes#yAcumulativoson valores intermedios requeridos para calcularCol_Offsetycolumnas.
La fórmula para resumir los totales de datos por mes y cliente se puede escribir en un formato desglosado como
=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)
o, como una fórmula completa
=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))
donde se supone que los metadatos y los datos diarios están contenidos en la hoja de trabajo Sheet1.
Esta fórmula se aplica a enero/Cliente 1 y se puede copiar a las celdas restantes para otros meses y clientes, como se muestra parcialmente en la captura de pantalla a continuación. Asegúrese de que los nombres de los meses utilizados en los metadatos y la tabla de resultados coincidan exactamente para evitar #N/Aerrores en los resultados.
Los argumentos segundo y cuarto de la OFFSETfunción son 1 y 6 porque los datos comienzan en la primera fila después de la celda resaltada en amarillo y, en el ejemplo que se muestra, hay 6 filas de datos. Si hay un número diferente de filas, simplemente sustituya el valor correcto.
Los argumentos tercero y quinto simplemente usan la función VLOOKUPpara buscar elCol_Offsetycolumnasvalores en los metadatos del mes involucrado.