Excel já tem funções como a SUMIF e a SUMIFS para somar os dados por grupos. No entanto, elas não podem funcionar se você tiver valores de data e hora combinados. Este artigo mostra como somar por data no Excel sem tempo usando as funções SUMPRODUCT e INT.
Syntax
=SUMPRODUCT( intervalo de valores * ( INT( intervalo de data e hora ) = célula de data ) )
Passos
- Inicie com =SUMPRODUCT( função
- Tipo de seleção do intervalo que contém os valores $C$3:$C$16
- Utilizar asterisco para valores de intervalo de produtos com intervalo de condições e abrir um parêntese *(
- Continuar com INT($B$3:$B$16) para remover a parte de tempo
- Entrar condição com um sinal e intervalo de critérios iguais =E3
- Tipo )) para fechar a função e terminar a fórmula
Como
Excel mantém os valores de data e hora como números. O Excel assume que o histórico começa em 1º de janeiro de 1900 e aceita esta data como 1. Enquanto números inteiros representam dias, o decimal representa o tempo. Por exemplo; 1/1/2018 é igual a 43101, 12:00 é igual a 0.5.
Podemos usar a função INT para nos livrarmos dos valores de tempo ou da parte decimal dos números. A função INT simplesmente retorna a parte inteira dos números a partir de um valor numérico. No nosso caso;
INT($B$3:$B$16) retorna {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
No entanto; para fazer a função INT funcionar em um array, precisamos usar a função SUMPRODUCT. A capacidade da função SUMPRODUCT de lidar com arrays sem usar fórmulas de array fornece uma vantagem sobre as limitações das funções SUMIF e SUMIFS. O SUMPRODUCT pode avaliar e retornar matrizes a partir de condições de intervalo – valor. Usamos esta funcionalidade para resolver intervalos de critérios – pares de critérios.
(INT($B$3:$B$16)=E3) retorna {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
A mesma forma, a função SUMPRODUCT soma os valores em seu array de argumentos se houver apenas um argumento. Entretanto, as equações condicionais retornam valores booleanos. Portanto, elas precisam ser convertidas para números que são significativos para a função SUMPRODUCT. Felizmente, o operador do produto (*) faz isto.
Multiplicando um array de valores com um array de condições, obtemos os valores do array gerados de acordo com nossas necessidades.
$C$3:$C$16*(INT($B$3:$B$16)=E3) retorna {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0}
Finalmente, o SUMPRODUCT retorna os resultados como soma por data no Excel que é o que estamos procurando.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) retorna 10 para a data de 7/11/2018.
Opções Alternativas
Uma forma alternativa é usar a PivotTable que pode dar-lhe mais opções do que valores de soma. Você pode encontrar informações detalhadas em Como agrupar dias sem tempo por Tabela Pivot.
Veja também os artigos relacionados como calcular a média ponderada com SUMPRODUCT, como agrupar intervalos 2d com SUMPRODUCT, e como somar valores com operador OR usando SUMPRODUCT com múltiplos critérios.