Sumar valores por año en Excel
Supongamos que tiene un rango de datos que contiene una lista de fechas y montos de venta, ahora desea obtener el monto total en función de los años dados, como se muestra a continuación. En Excel, las funciones normales SUMIFS y SUMPRODUCT pueden ayudar a resolver esta tarea de forma rápida y sencilla. Este tutorial demostrará cómo utilizar estas dos funciones en detalle.
- Sumar valores por año en Excel con funciones SUMIFS y FECHA
- Sumar valores por año en Excel con la función SUMPRODUCTO
Sumar valores por año en Excel con funciones SUMIFS y FECHA
Para sumar el valor total basado en un año determinado, debe especificar la primera y la última fecha del año usando la función FECHA, y luego usar la función SUMAIFS para sumar todos los valores dentro de las fechas especificadas, la sintaxis genérica es:
- sum_range: El rango de valores que se van a sumar;
- date_range: El rango de fechas que se van a probar para el año específico;
- year: El año específico en el que desea sumar valores. (año, 1,1 indica el primer día del año, y año, 12,31 indica el último día del año.)
Para resolver esta tarea en Excel, copie o ingrese la fórmula a continuación en una celda en blanco, y luego arrastre hacia abajo la fórmula a otras celdas para obtener el resultado:
Explicación de esta fórmula:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- $ C $ 2: $ C $ 12: es el "rango_suma" que contiene los valores que queremos sumar.
- $ B $ 2: $ B $ 12, "> =" & FECHA (E2,1,1): comprueba las fechas en el rango B2: B12 que son mayores o iguales que 01/01/2020, el primer día del año en la celda E2.
- $ B $ 2: $ B $ 12, "<=" & FECHA (E2,12,31): comprueba las fechas en el rango B2: B12 que son menores o iguales al 12/31/2020, el último día del año en la celda E2.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): Después de cumplir con los criterios, los valores en el rango C2: C12 se encontrarán y se sumarán.
Sumar valores por año en Excel con la función SUMPRODUCTO
Aquí, otra fórmula simple que es creada por la función SUMPRODUCT también puede ayudarlo, la sintaxis genérica es:
- date_range: El rango de fechas que se van a probar para el año específico;
- year: El año específico en el que desea sumar valores;
- sum_range: El rango de valores que se van a sumar.
Copie o ingrese la siguiente fórmula en una celda en blanco y luego arrastre hacia abajo la fórmula a otras celdas para obtener el resultado:
Explicación de esta fórmula:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- AÑO ($ B $ 2: $ B $ 12) = E2: La función AÑO devuelve el número de año de cada celda de fecha en el rango B2: B12. Esta expresión comprueba si el año en E2 es igual al año en el rango B2: B12. Y obtendrá una matriz como esta: {VERDADERO; FALSO; VERDADERO; VERDADERO; FALSO; VERDADERO; VERDADERO; FALSO; FALSO; VERDADERO; FALSO}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: En esta expresión, los VERDADEROS y FALSOS se forzarán a 1 y 0, y esta matriz se multiplica por cada cantidad en el rango C2: C12, y obtendrá una nueva matriz como esta: {347; 0; 398; 430; 0; 379; 412; 0; 0; 420; 0}.
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): La función SUMPRODUCTO finalmente suma todos los números en esta matriz y devuelve el resultado como 2386.
Función relativa utilizada:
- SUMIFS:
- La función SUMIFS en Excel puede ayudar a sumar valores de celda según múltiples criterios.
- SUMPRODUCT:
- La función SUMPRODUCTO se puede usar para multiplicar dos o más columnas o matrices juntas y luego obtener la suma de productos.
- FECHA:
- La función FECHA puede ayudarnos a combinar números de año, mes y día de celdas separadas para una fecha válida.
- AÑO:
- La función AÑO devuelve el año en función de la fecha dada en un formato de número de serie de 4 dígitos.
Más artículos:
- Suma de valores por mes (con o sin año)
- Este tutorial muestra dos fórmulas en detalle para ayudar a sumar valores por cierto mes y año o sumar valores por cierto mes ignorando el año en Excel.
- Suma de valores por semana de una fecha determinada
- Para sumar valores por la semana en la que cae una fecha determinada, puede aplicar una fórmula basada en la función SUMIFS.
- Suma de valores por número de semana o día de la semana
- Este tutorial muestra fórmulas paso a paso para ayudarlo a sumar valores en un cierto rango según el número de semana o día de la semana especificado en Excel.
Las mejores herramientas de productividad de oficina
Kutools para Excel: te ayuda a destacar entre la multitud
Kutools para Excel cuenta con más de 300 funciones, Garantizar que lo que necesita está a sólo un clic de distancia...
Ficha Office: habilite la lectura y edición con pestañas en Microsoft Office (incluya Excel)
- ¡Un segundo para cambiar entre docenas de documentos abiertos!
- Reduzca cientos de clics del mouse para usted todos los días, diga adiós a la mano del mouse.
- Aumenta su productividad en un 50% al ver y editar varios documentos.
- Trae pestañas eficientes a Office (incluido Excel), al igual que Chrome, Edge y Firefox.