Excel ya tiene funciones como el SUMIF y el SUMIFS para sumar datos por grupos. Sin embargo; no pueden funcionar si tienes valores fecha-hora combinados. Este artículo muestra Cómo sumar por fecha en Excel sin tiempo utilizando las funciones SUMPRODUCT e INT.
Sintaxis
=SUMPRODUCT( rango de valores * ( INT( rango de fecha-hora ) = celda de fecha )
Pasos
- Empezar con =SUMPRODUCT( función
- Tipo de seleccionar el rango que contiene valores $C$3:$C$16
- Utilizar asterisco para producir los valores del rango con la condición rango y abrir un paréntesis *(
- Continuar con INT($B$3:$B$16) para eliminar la parte del tiempo
- Introducir la condición con un signo igual y el rango de criterios =E3
- Tipo )) para cerrar la función y terminar la fórmula
Cómo
Excel mantiene los valores de fecha y hora como números. Excel asume que el historial comienza desde el 1 de enero de 1900 y acepta esta fecha como 1. Mientras que los números enteros representan los días, el decimal representa el tiempo. Por ejemplo; 1/1/2018 es igual a 43101, 12:00 es igual a 0.5.
Podemos utilizar la función INT para deshacernos de los valores de tiempo o de la parte decimal de los números. La función INT simplemente devuelve la parte numérica entera de un valor numérico. En nuestro caso;
INT($B$3:$B$16) devuelve {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Sin embargo; para hacer que la función INT trabaje en un array, necesitamos utilizar la función SUMPRODUCT. La capacidad de la función SUMPRODUCT para manejar matrices sin utilizar fórmulas de matrices proporciona una ventaja sobre las limitaciones de las funciones SUMIF y SUMIFS. La función SUMPRODUCT puede evaluar y devolver arrays a partir de condiciones de rango-valor. Utilizamos esta funcionalidad para resolver pares de rango – criterio.
(INT($B$3:$B$16)=E3) devuelve {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Además, la función SUMPRODUCT suma los valores de su matriz de argumentos si sólo hay un argumento. Sin embargo, las ecuaciones condicionales devuelven valores booleanos. Por lo tanto, es necesario convertirlos en números que sean significativos para la función SUMPRODUCT. Afortunadamente, el operador producto (*) hace esto.
Multiplicando un array de valores con un array de condiciones, obtenemos los valores del array generados según nuestras necesidades.
$C$3:$C$16*(INT($B$3:$B$16)=E3) devuelve {1;2;3;4;0;0;0;0;0;0;0;0}
Finalmente, el SUMPRODUCTO devuelve los resultados como suma por fecha en Excel que es lo que buscamos.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) devuelve 10 para la fecha de 7/11/2018.
Opción alternativa
Una forma alternativa es utilizar PivotTable que puede dar más opciones que sumar valores. Puede encontrar información detallada en Cómo agrupar días sin hora mediante Pivot Table.
También puede ver los artículos relacionados cómo calcular la media ponderada con SUMPRODUCT, cómo SUMAR rangos de 2d con SUMPRODUCT, y cómo sumar valores con el operador OR usando SUMPRODUCT con múltiples criterios.