Resumen
Para sumar por mes en columnas, puede usar la función SUMIFS junto con la función EOMONTH. En el ejemplo que se muestra, la fórmula en G5 es:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Esta fórmula utiliza tres rangos con nombre: "monto" (D5: D15), "cliente" (B5: B15) y "fecha" (C5: C15).
Explicación
La función SUMIFS está diseñada para sumar valores en un rango basado en uno o más criterios. En este caso, necesitamos tres criterios:
- Nombre del cliente = cliente en la columna F
- Fecha> = primer día de mes (desde la fecha en la fila 4)
- Fecha <= fin de mes (desde la fecha en la fila 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 SUMIFS, el primer par de rango / criterio se usa para verificar el nombre del cliente:
client,$F5
Observe que 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 de rango / criterio se usa para comparar las fechas con el 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 fechas mayores que el primero del 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 se copia la fórmula 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 de rango / criterio se usa para comparar las fechas con el último día del mes:
date,"<="&EOMONTH(G$4,0)
Para obtener el último día de cada mes, usamos la función EOMONTH en la fecha del encabezado de la columna en la fila 4. Con cero proporcionado para el argumento de meses, EOMONTH 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 se puede agrupar automáticamente por mes sin ninguna fórmula. Para ver una comparación en paralelo de fórmulas y tablas dinámicas, vea este video: Por qué las tablas dinámicas.
