EXCEL: Suma por mes en columnas

Para sumar por mes en columnas, puede usar la función SUMAR.SI.CONJUNTO junto con la función FIN.MES. En el ejemplo que se muestra, la fórmula en G5 es:

=SUMAR.SI.CONJUNTO(amount,client,$F5,date,">="&G$4,date,"<="&FIN.MES(G$4,0))

Esta fórmula utiliza tres rangos con nombre: "cantidad" (D5: D15), "cliente" (B5: B15) y "fecha" (C5: C15).

Cómo funciona esta fórmula

La función SUMAR.SI.CONJUNTO está diseñada para sumar valores en un rango basado en uno o más criterios. En este caso, necesitamos tres criterios:

1. Client name = client in column F
2. Date >= first of month (from date in row 4)
3. Date <= end of month (from date in row 4)

Punto clave: los nombres de los meses en G4: I4 son en realidad fechas válidas, formateadas con el formato de número personalizado "mmm". Esto nos permite usar los valores de fecha en G4: I4 directamente para los criterios # 2 y # 3 anteriores.

De vuelta en SUMAR.SI.CONJUNTO, el primer par rango / criterio se usa para verificar el nombre del cliente:

client,$F5

El aviso F5 es una referencia mixta, con la columna bloqueada. Esto permite que la fila cambie a medida que la fórmula se copia a través de la tabla, pero el nombre del cliente siempre proviene de la columna F.

El segundo par rango / criterio se usa para verificar las fechas con respecto al primero del mes:

date,">="&G$4

Como se mencionó anteriormente, los valores en G4: I4 son en realidad fechas: 1 de enero de 2019, 1 de febrero de 2019 y 1 de marzo de 2019. Por lo tanto, este criterio simplemente verifica las fechas mayores que las del primer mes en la fila 4. Observe que Esta es otra referencia mixta, esta vez con la fila bloqueada. Esto permite que la columna cambie a medida que la fórmula se copia en la tabla, pero mantiene fijo el número de fila. La concatenación con un ampersand (&) es necesaria cuando se crean criterios que utilizan un operador lógico y un valor de otra celda.

El tercer par rango / criterio se utiliza para verificar las fechas con respecto al último día del mes:

date,"<="&FIN.MES(G$4,0)

Para obtener el último día de cada mes, utilizamos la función FIN.MES en la fecha del encabezado de la columna en la fila 4. Con cero proporcionado para el argumento de los meses, FIN.MES devuelve el último día del mismo mes. Nuevamente, la referencia a G4 se mezcla para evitar que la fila cambie.

Solución de tabla dinámica

Una tabla dinámica sería una excelente solución para este problema, ya que puede agruparse automáticamente por mes sin ninguna fórmula. Para una comparación lado a lado de fórmulas frente a tablas dinámicas, vea este video: ¿Por qué las tablas dinámicas?