Somar valores por ano no Excel

Suponha que você tenha um intervalo de dados que contém uma lista de datas e valores de vendas, e agora deseja obter o valor total com base em anos específicos, conforme mostrado na captura de tela abaixo. No Excel, as funções normais SOMASES e SOMARPRODUTO podem ajudar a resolver essa tarefa rapidamente e facilmente. Este tutorial demonstrará como usar essas duas funções em detalhes.

• Somar valores por ano no Excel com as funções SOMASES e DATA
• Somar valores por ano no Excel com a função SOMARPRODUTO

Somar valores por ano no Excel com as funções SOMASES e DATA

Para somar o valor total com base em um determinado ano, você deve especificar a primeira e a última data do ano usando a função DATA, e depois usar a função SOMASES para somar todos os valores dentro das datas especificadas. A sintaxe genérica é:

• intervalo_soma: O intervalo de valores que serão somados;
• intervalo_datas: O intervalo de datas a serem testadas para o ano específico;
• ano: O ano específico para o qual você deseja somar os valores. (ano, 1,1 indica o primeiro dia do ano, e ano,12,31 indica o último dia do ano.)

Para resolver esta tarefa no Excel, copie ou insira a fórmula abaixo em uma célula em branco e arraste a fórmula para outras células para obter o resultado:

Explicação desta fórmula:

=SOMASES($C$2:$C$12,$B$2:$B$12,">="&DATA(E2,1,1),$B$2:$B$12,"<="&DATA(E2,12,31))

• $C$2:$C$12: é o “intervalo_soma” que contém os valores que queremos somar.
• $B$2:$B$12,">="&DATA(E2,1,1): verifica as datas no intervalo B2:B12 que são maiores ou iguais a 01/01/2020 – o primeiro dia do ano na célula E2.
• $B$2:$B$12,"<="&DATA(E2,12,31): verifica as datas no intervalo B2:B12 que são menores ou iguais a 31/12/2020 – o último dia do ano na célula E2.
• SOMASES($C$2:$C$12,$B$2:$B$12,">="&DATA(E2,1,1),$B$2:$B$12,"<="&DATA(E2,12,31)): Após atender aos critérios, os valores no intervalo C2:C12 serão encontrados e somados.

Somar valores por ano no Excel com a função SOMARPRODUTO

Aqui, outra fórmula simples criada pela função SOMARPRODUTO também pode ajudá-lo, a sintaxe genérica é:

• intervalo_datas: O intervalo de datas a serem testadas para o ano específico;
• ano: O ano específico para o qual você deseja somar os valores;
• intervalo_soma: O intervalo de valores a serem somados.

Por favor, copie ou insira a seguinte fórmula em uma célula em branco e arraste a fórmula para outras células para obter o resultado:

Explicação desta fórmula:

=SOMARPRODUTO((ANO($B$2:$B$12)=E2)*$C$2:$C$12)

• ANO($B$2:$B$12)=E2:A função ANO retorna o número do ano de cada célula de data no intervalo B2:B12. Esta expressão verifica se o ano em E2 é igual ao ano no intervalo B2:B12. E você obterá uma matriz assim: {VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}.
• (ANO($B$2:$B$12)=E2)*$C$2:$C$12={VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}*$C$2:$C$12: Nesta expressão, os VERDADEIROS e FALSOS serão forçados a 1s e 0s, e esta matriz será multiplicada por cada valor no intervalo C2:C12, e você obterá uma nova matriz assim: {347;0;398;430;0;379;412;0;0;420;0}.
• SOMARPRODUTO((ANO($B$2:$B$12)=E2)*$C$2:$C$12)= SOMARPRODUTO({347;0;398;430;0;379;412;0;0;420;0}): A função SOMARPRODUTO finalmente soma todos os números nesta matriz e retorna o resultado como 2386.

Função relacionada usada:

Mais artigos: