Fórmula genérica
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Resumo
Para contar novos clientes por mês, você pode usar uma coluna auxiliar e a função COUNTIFS. No exemplo mostrado, a fórmula em H5 é:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
onde "novo" (E5: E15) e "data" (C5: C15) são intervalos nomeados.
Explicação
Esta fórmula depende de uma coluna auxiliar, que é a coluna E no exemplo mostrado. A fórmula em E5, copiada, é:
=(COUNTIFS($B$5:B5,B5)=1)+0
Esta fórmula retorna 1 para novos clientes e 0 para clientes recorrentes e é explicada em detalhes aqui. Depois que essa fórmula estiver em vigor, a função COUNTIFS pode ser usada para contar novos clientes a cada mês.
O primeiro intervalo e critérios dentro de COUNTIFS contam com 1 na coluna "novo":
=COUNTIFS(new,1
sem outros critérios, isso retornaria uma contagem de todos os clientes únicos nos dados. No entanto, queremos uma contagem por mês, portanto, precisamos restringir a contagem a cada mês mostrado na coluna G.
Os nomes dos meses na coluna G são, na verdade, as datas do "primeiro dia do mês": 1 de janeiro de 2019, 1 de fevereiro de 2019 e 1 de março de 2019. As datas são formatadas com o formato de número do cliente "mmm" para serem exibidas como nomes de mês de 3 letras: Isso nos permite escrever critérios simples para contar por mês usando as datas diretamente.
Para limitar a contagem a apenas 1s que sejam maiores ou iguais ao primeiro dia do mês na coluna G, usamos o intervalo nomeado "data" concatenado ao operador maior ou igual a:
=COUNTIFS(new,1,date,">="&G5
Para limitar ainda mais a contagem para incluir apenas 1 que ocorre no final de cada mês, adicionamos um último par de intervalo / critério:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Aqui, novamente, usamos o intervalo denominado "data" e concatenamos o operador menor ou igual a (<=) ao último dia do mês, criado com a função EOMONTH.
Conforme essa fórmula é copiada, ela retorna a contagem de novos clientes em cada mês.
Clientes recorrentes por mês
Para contar os clientes repetidos por mês, você pode ajustar a fórmula para contar zeros em vez de 1s. A fórmula em I5, copiada, é:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
