Resumo
Para somar por mês em colunas, você pode usar a função SUMIFS junto com a função EOMONTH. No exemplo mostrado, a fórmula em G5 é:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Esta fórmula usa três intervalos nomeados: "montante" (D5: D15), "cliente" (B5: B15) e "data" (C5: C15).
Explicação
A função SUMIFS foi projetada para somar valores em um intervalo com base em um ou mais critérios. Nesse caso, precisamos de três critérios:
- Nome do cliente = cliente na coluna F
- Data> = primeiro dia do mês (a partir da data na linha 4)
- Data <= final do mês (a partir da data na linha 4)
Ponto principal: os nomes dos meses em G4: I4 são realmente datas válidas, formatadas com o formato de número personalizado "mmm". Isso nos permite usar os valores de data em G4: I4 diretamente para os critérios 2 e 3 acima.
De volta ao SUMIFS, o primeiro par de intervalo / critério é usado para verificar o nome do cliente:
client,$F5
Observe que F5 é uma referência mista, com a coluna bloqueada. Isso permite que a linha mude conforme a fórmula é copiada na tabela, mas o nome do cliente sempre vem da coluna F.
O segundo par de intervalo / critério é usado para verificar as datas em relação ao primeiro dia do mês:
date,">="&G$4
Conforme mencionado acima, os valores em G4: I4 são, na verdade, datas: 1 ° de janeiro de 2019, 1 ° de fevereiro de 2019 e 1 ° de março de 2019. Portanto, este critério apenas verifica datas superiores às datas do primeiro mês na linha 4. Observe que esta é outra referência mista, desta vez com a linha bloqueada. Isso permite que a coluna mude conforme a fórmula é copiada na tabela, mas mantém o número da linha fixo. A concatenação com um E comercial (&) é necessária ao construir critérios que usam um operador lógico e um valor de outra célula.
O terceiro par de intervalo / critério é usado para verificar as datas em relação ao último dia do mês:
date,"<="&EOMONTH(G$4,0)
Para obter o último dia de cada mês, usamos a função EOMONTH na data do cabeçalho da coluna na linha 4. Com zero fornecido para o argumento de meses, o EOMONTH retorna o último dia do mesmo mês. Novamente, a referência a G4 é misturada para evitar que a linha mude.
Solução de tabela dinâmica
Uma tabela dinâmica seria uma excelente solução para este problema, porque pode agrupar automaticamente por mês, sem qualquer fórmula. Para uma comparação lado a lado de fórmulas e tabelas dinâmicas, veja este vídeo: Por que tabelas dinâmicas.
