Baixe a apostila de exemplo
Este tutorial demonstrará como usar a função SUMIFS para somar dados correspondentes a meses específicos no Excel e nas planilhas do Google.
Soma por mês
Primeiro, vamos demonstrar como somar os dados correspondentes às datas que caem em um determinado mês e ano.
Podemos usar a função SUMIFS, junto com as funções DATE, YEAR, MONTH e EOMONTH para somar as Número de Vendas dentro de cada Mês.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATA (ANO (E3), MÊS (E3), 1), B3: B9, "<=" & EOMONTE (E3,0)) |
Para construir a fórmula acima, começamos definindo o intervalo de datas para cada mês. Usamos a função DATE para definir o primeiro dia do mês (ex. 01/05/2021). Podemos fazer isso “embutindo em código” a data na fórmula:
| 1 | "> =" & DATA (2021,5,1) |
Ou, melhor ainda, podemos tornar os critérios flexíveis referindo-nos a uma data dentro da célula E3, extraindo o ano e o mês da data (e definindo o dia igual a 1), conforme mostrado no exemplo acima:
| 1 | "> =" & DATA (ANO (E3), MÊS (E3), 1) |
Para definir o último dia do mês, podemos usar a função EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Colocando todos esses critérios juntos, podemos escrever a seguinte fórmula SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATA (ANO (E3), MÊS (E3), 1), B3: B9, "<=" & EOMONT (E3,0)) |
Referências de Célula de Bloqueio
Para tornar nossas fórmulas mais fáceis de ler, mostramos as fórmulas sem referências de células bloqueadas:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATA (ANO (E3), MÊS (E3), 1), B3: B9, "<=" & EOMONT (E3,0)) |
Mas essas fórmulas não funcionarão corretamente quando copiadas e coladas em outro lugar do arquivo. Em vez disso, você deve usar referências de células bloqueadas como este:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATA (ANO (E3), MÊS (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Leia nosso artigo sobre Bloqueio de referências de células para saber mais.
Formatando valores de mês
Neste exemplo, listamos os meses na coluna E. Esses valores de mês são, na verdade, datas formatadas para omitir o dia, usando a formatação de número personalizada.
O formato de dados personalizados é “mmm aaaa” para mostrar maio de 2022.
Soma por mês em vários anos
O exemplo acima somou dados com datas que caíram dentro de um mês e ano específicos. Em vez disso, você pode somar dados com datas que caem dentro de um mês em qualquer ano usando a função SUMPRODUCT.
| 1 | = SUCESSO (C3: C8, - (MÊS (B3: B8) = MÊS (G3))) |
Neste exemplo, usamos a função SUMPRODUCT para realizar cálculos complicados de “soma se”. Vamos examinar a fórmula.
Esta é a nossa fórmula final:
| 1 | = SUCESSO (C3: C8, - (MÊS (B3: B8) = MÊS (G3))) |
Primeiro, a função SUMPRODUCT lista o Número de Vendas para cada Data de Venda e então compara o mês de cada Data de Venda contra o especificado Mês, retornando TRUE se os meses corresponderem ou FALSE se não:
| 1 | = SUMPRODUTO ({30; 42; 51; 28; 17; 34}, - ({VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO})) |
Em seguida, os travessões duplos (-) convertem os valores VERDADEIRO e FALSO em 1s e 0s:
| 1 | = SUMPRODUTO ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
A função SUMPRODUCT multiplica cada par de entradas nas matrizes:
| 1 | = SUMPRODUTO ({30; 0; 51; 0; 17; 0}) |
Finalmente, os números na matriz são somados:
| 1 | =98 |
Mais detalhes sobre o uso de declarações booleanas e o comando “-” em uma função SUMPRODUCT podem ser encontrados aqui
Soma por mês no Planilhas Google
Essas fórmulas funcionam exatamente da mesma forma no Planilhas Google e no Excel.
