Muchas veces queremos calcular algunos valores por mes. Por ejemplo, cuántas ventas se realizaron en un mes en particular. Bueno, esto se puede hacer fácilmente usando tablas dinámicas, pero si está tratando de tener un informe dinámico, podemos usar una fórmula SUMPRODUCT o SUMIFS para sumar por mes.
Comencemos con la solución SUMPRODUCT.
Aquí está la fórmula genérica para obtener la suma por mes en Excel
= SUMPRODUCTO (rango_suma, - (TEXTO (rango_fecha, "MMM") = texto_mes))
Rango suma : Es el rango que desea sumar por mes.
Rango de fechas : Es el intervalo de fechas que buscará durante meses.
Month_text: Es el mes en formato de texto del que desea sumar valores.
Ahora veamos un ejemplo:
Ejemplo: sumar valores por mes en Excel
Aquí tenemos algún valor asociado con las fechas. Estas fechas son los meses de enero, febrero y marzo del año 2019.
Como puede ver en la imagen de arriba, todas las fechas son del año 2019. Ahora solo necesitamos sumar valores en E2: G2 por meses en E1: G1.
Ahora, para sumar valores según meses, escriba esta fórmula en E2:
= SUMPRODUCTO (B2: B9, - (TEXTO (A2: A9, "MMM") = E1)))
Si desea copiarlo en celdas adyacentes, use referencias absolutas o rangos con nombre como en la imagen.
Esto nos da la suma exacta de cada mes.
¿Cómo funciona?
Empezando por el interior, veamos el TEXTO (A2: A9, "MMM") parte. Aquí la función TEXTO extrae el mes de cada fecha en el rango A2: A9 en formato de texto en una matriz. Traduciendo a fórmula a = SUMPRODUCTO (B2: B9, - ({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
A continuación, TEXTO (A2: A9, "MMM")= E1: Aquí, cada mes de la matriz se compara con el texto de E1. Dado que E1 contiene "Jan", cada "Jan" de la matriz se convierte en VERDADERO y el resto en FALSO. Esto traduce la fórmula a = SUMPRODUCTO ($ B $ 2: $ B $ 9, - {VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO; FALSO; VERDADERO; FALSO})
Siguiente - (TEXT (A2: A9, "MMM") = E1), convierte VERDADERO FALSO en valores binarios 1 y 0. La fórmula se traduce en = SUMPRODUCTO ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Finalmente SUMPRODUCTO($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): la función SUMPRODUCTO multiplica los valores correspondientes en $ B $ 2: $ B $ 9 a la matriz {1; 1; 0; 1; 0; 0; 1; 0} y los suma. Por lo tanto, obtenemos la suma por valor como 20052 en E1.
SUMA SI Meses de Año Diferente
En el ejemplo anterior, todas las fechas eran del mismo año. ¿Y si fueran de diferentes años? La fórmula anterior sumará valores por mes independientemente del año. Por ejemplo, se agregarán enero de 2018 y enero de 2019, si usamos la fórmula anterior. Lo que está mal en la mayoría de los casos.
Esto sucederá porque no tenemos ningún criterio para el año en el ejemplo anterior. Si también agregamos criterios de año, funcionará.
La fórmula genérica para obtener la suma por mes y año en Excel
= SUMPRODUCTO (rango_suma, - (TEXTO (rango_fecha, "MMM") = texto_memes), - (TEXTO (rango_fecha, "aaaa") = TEXTO (año, 0)))
Aquí, hemos agregado un criterio más que verifica el año. Todo lo demás es lo mismo.
Resolvamos el ejemplo anterior, escriba esta fórmula en la celda E1 para obtener la suma de enero en el año 2017.
= SUMPRODUCTO (B2: B9, - (TEXTO (A2: A9, "MMM") = E1), - (TEXTO (A2: A9, "aaaa") = TEXTO (D2,0)))
Antes de copiar en las siguientes celdas, use rangos con nombre o referencias absolutas. En la imagen, he usado rangos con nombre para copiar en las celdas adyacentes.
Ahora podemos ver la suma del valor por meses y años también.
¿Como funciona?
La primera parte de la fórmula es la misma que en el ejemplo anterior. Entendamos la parte adicional que agrega los criterios del año.
- (TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") convierte la fecha en A2: A9 como años en formato de texto en una matriz. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
La mayoría de las veces, el año se escribe en formato numérico. Para comparar un número con texto, he convertido año int texto usando TEXT (D2,0). A continuación, comparamos este año de texto con una matriz de años como TEXT (A2: A9, "yyyy") = TEXT (D2,0). Esto devuelve una matriz de verdadero-falso {FALSO; FALSO; VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO}. A continuación, convertimos verdadero falso en número usando el operador -. Esto nos da {0; 0; 1; 1; 0; 1; 0; 1}.
Entonces, finalmente, la fórmula se traducirá a = SUMPRODUCTO (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Donde la primera matriz son valores. El siguiente es el mes emparejado y el tercero es el año. Finalmente obtenemos nuestra suma de valores como 2160.
Usando la función SUMIFS para sumar por mes
Fórmula genérica
= SUMIFS (rango_suma, rango_fecha, ”> =” & fecha_inicio, rango_fecha, ”<=” & EOMONTH (fecha_inicio, 0))
Aquí,Rango suma : Es el rango que desea sumar por mes.
Rango de fechas : Es el intervalo de fechas que buscará durante meses.
Fecha de inicio : Es la fecha de inicio a partir de la cual desea sumar. Para este ejemplo, será el primero del mes dado.
Ejemplo: sumar valores por mes en Excel
Aquí tenemos algún valor asociado con las fechas. Estas fechas son los meses de enero, febrero y marzo del año 2019.
Solo necesitamos sumar estos valores por mes. Ahora era fácil si tuviéramos meses y años separados. Pero no lo son. No podemos utilizar ninguna columna de ayuda aquí.
Entonces, para preparar el informe, he preparado un formato de informe que contiene el mes y la suma de valores. En la columna del mes, tengo una fecha de inicio del mes. Para ver solo el mes, seleccione la fecha de inicio y presione CTRL + 1.
En formato personalizado, escriba "mmm".
Ahora tenemos nuestros datos listos. Sumemos los valores por mes.
Escriba esta fórmula en E3 para sumar por mes.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Utilice referencias absolutas o rangos con nombre antes de copiar la fórmula.
Entonces, finalmente obtuvimos el resultado.
Entonces, ¿cómo funciona?
Como sabemos, la función SUMIFS puede sumar valores en múltiples criterios.
En el ejemplo anterior, el primer criterio es sumar todos los valores en B3: B10 donde, la fecha en A3: A10 es mayor o igual que la fecha en D3. D3 contiene el 1 de enero. Esto también se traduce.
= SUMIFS (B3: B10, A3: A10, "> =" & “1-ene-2019”, A3: A10, "<=" EOMONTH (D3,0))
El siguiente criterio es la suma solo si la fecha en A3: A10 es menor o igual que EOMONTH (D3,0). La función EOMONTH solo devuelve el número de serie de la última fecha del mes proporcionado. Finalmente, la fórmula también se traduce.
= SUMIFS (B3: B10, A3: A10, "> = 1-ene-2019”, A3: A10, "<= 31-ene-2019”)
Por lo tanto, obtenemos la suma por mes en Excel.
El beneficio de este método es que puede ajustar la fecha de inicio para sumar los valores.
Si sus fechas tienen diferentes años, es mejor usar tablas dinámicas. Las tablas dinámicas pueden ayudarlo a segregar los datos en formato anual, trimestral y mensual fácilmente.
Así que sí, chicos, así es como pueden sumar valores por mes. Ambas formas tienen sus propias especialidades. Elige la forma que más te guste.
Si tiene alguna consulta con respecto a este artículo o cualquier otra consulta relacionada con Excel y VBA, la sección de comentarios está abierta para usted.
Relacionar artículos:
Cómo usar la función SUMIF en Excel
SUMIFS con fechas en Excel
SUMIF con celdas que no están en blanco
Cómo utilizar la función SUMIFS en Excel
SUMIFS usando lógica AND-OR
articulos populares
50 atajos de Excel para aumentar su productividad: Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar tLa función BUSCARV en Excel: Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar la función CONTAR.SI en Excel: Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo utilizar la función SUMIF en Excel: Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
