Soma os valores por ano no Excel

Supondo que você tenha uma gama de dados que contém uma lista de datas e valores de venda, agora você deseja obter o valor total com base em determinados anos, conforme a imagem mostrada abaixo. No Excel, as funções normais de SUMIFS e SUMPRODUCT podem ajudar a resolver essa tarefa de forma rápida e fácil. Este tutorial demonstrará como usar essas duas funções em detalhes.

• Soma os valores por ano no Excel com as funções SUMIFS e DATE
• Soma os valores por ano no Excel com a função SUMPRODUCT

---

Soma os valores por ano no Excel com as funções SUMIFS e DATE

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 DATE e, em seguida, usar a função SUMIFS para somar todos os valores nas datas especificadas, a sintaxe genérica é:

• sum_range: O intervalo de valores que devem ser somados;
• date_range: O intervalo de datas a serem testados para o ano específico;
• year: O ano específico no 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 essa 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:

=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: é 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 12/31/2020 - o último dia do ano na célula E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): Depois de cumprir os critérios, os valores no intervalo C2: C12 serão encontrados e somados.

---

Soma os valores por ano no Excel com a função SUMPRODUCT

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

• date_range: O intervalo de datas a serem testados para o ano específico;
• year: O ano específico em que você deseja somar os valores;
• sum_range: O intervalo de valores a serem somados.

Copie ou insira a fórmula a seguir em uma célula em branco e arraste a fórmula para outras células para obter o resultado:

---

Explicação desta fórmula:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• ANO ($ B $ 2: $ B $ 12) = E2: A função YEAR 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 como esta: {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}.
• (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: Nesta expressão, os TRUEs e FALSEs serão forçados a 1s e 0s, e esta matriz é multiplicada por cada valor no intervalo C2: C12, e você obterá uma nova 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}): A função SUMPRODUCT finalmente soma todos os números nesta matriz e retorna o resultado como 2386.

---

Função relativa usada:

---

Mais artigos: