A pedido por MP, a explicação da fórmula:
=SOMA(SE(FREQUÊNCIA((E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0));(E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0)))>0;1))-1
Amigo, é um tanto complicado explicar, vou tentar detalhar, mas pode ser um pouco difícil de entender. Uma coisa que vc pode (e deve) fazer, é buscar o entendimento de cada uma das funções em separado (SOMA, SE, FREQUÊNCIA, CORRESP). Também é bom que vc acompanhe o cálcula da fórmula, selecione a célula com a fórmula, vá no menu fórmula e clique em "avaliar fórmula" e veja o passo a passo. Ou, selecione o trecho da fórmula que quer ver calculado (na barra de fórmulas), e clique em F9.
Mas, vamos lá:
Considera a função FREQUÊNCIA(), que traz a quantidade de ocorrências em uma lista (matriz).
Posso usá-la para saber, por exemplo, quantas vezes o número 2 aparece na lista {2; 3; 4; 5; 2; 1; 2}.
Mas a função trabalha com matrizes, então, eu vou procurar mais de um número, digamos, o 2 e o 3, que seriam { 2; 3}
Nesse exemplo simplificado,
A lista {2; 3; 4; 5; 2; 1; 2} seria o que eu destaquei abaixo:
=SOMA(SE(FREQUÊNCIA((E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0));(E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0)))>0;1))-1
E a lista {2; 3} seria o que eu destaquei abaixo:
=SOMA(SE(FREQUÊNCIA((E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0));(E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0)))>0;1))-1
Explicando esses trechos:
O primeiro
(E2:E11=B1)*(G2:G11>B2)*(CORRESP(F2:F11;F2:F11;0)
Esse esquema (que pode ser usado em várias funções, utiliza uma lógica booleana (verdadeiro ou falso).
Digamos que eu comparei o valor de E2 com o de B1, na forma E2=B1. O resultado seria verdadeiro ou falso. Porém, eu comparei a lista toda de E2 até E11, por isso: (E2:E11=B1).
O resultado disso seria uma sequência de verdadeiros ou falsos. Sabendo que o Excel entende verdadeiro como 1 e falso como 0, eu posso converter esses verdadeiros ou falsos em uns e zeros com uma multiplicação.
Nesse caso, pelo teu exemplo, comparando B1 (O representante procurado), com E2:E11 (A lista de representantes) vc teria:
{verdadeiro; verdadeiro; verdadeiro; verdadeiro; verdadeiro; falso; falso; falso; falso; falso}.
E, comparando B2 (a data procurada), com G2:G11 (A lista de datas) usando o sinal "maior que" > teria:
{verdadeiro; verdadeiro; verdadeiro; verdadeiro; falso; verdadeiro; verdadeiro; verdadeiro; verdadeiro}.
como multipliquei um pelo outro, eu teria o seguinte:
{1; 1; 1; 1; 1; 0; 0; 0; 0; 0} * {1; 1; 1; 1; 0; 1; 1; 1; 1; 1}
Sendo que o resultado disso seria:
{1; 1; 1; 1; 0; 0; 0; 0; 0; 0}
Ou seja, até ai, eu tenho apenas os ítens que se referem ao representante procurado, dentro da data especificada.
mas tem a parte do CORRESP(). Esta fórmula busca a informação, que no caso é o nome do cliente, e retorna a posição dele na lista (em F2:F11).
Mas, como eu preciso buscar todos os clientes, é um truque, na verdade. Isso porque o CORRESP sempre retorna o primeiro valor, assim, se JOSÉ aparece na linha 2 e 5, em ambas as linhas ele vai retornar 2. Esse truque "converte" os nomes em um número, hehe.
No caso, para os nomes, eu teria a matriz:
{1; 1; 3; 3; 1; 6; 6; 6; 9; 9}
Que, multiplicada, fica:
{1; 1; 1; 1; 0; 0; 0; 0; 0; 0} * {1; 1; 3; 3; 1; 6; 6; 6; 9; 9}
Ou seja:
{1; 1; 3; 3; 0; 0; 0; 0; 0; 0} que é a minha lista com números que eu quero contar a frequência.
Como vc deve ter notado, os 2 trechos que eu destaquei em vermelho na fórmula são o mesmo, o que significa que eu estou buscando a frequência dos {1; 1; 3; 3; 0; 0; 0; 0; 0; 0} (que são, na verdade os numeros {0; 1; 3} repetidos. Ou seja, se vc contar, terá:
2x o número 1
2x o número 3
5x o número 0
O que dá como resultado da fórmula FREQUÊNCIA: {2; 2; 5}
Aí é onde entra a função SE()
Ela vai avaliar o que for maior que 0. se for maior que zero, será 1, senão, será 0. Assim, converte a matriz: {2; 2; 5} para {1; 1; 1}.
Depois disso, é que utilizo a função SOMA().
Ela vai somar {1; 1; 1}, ou seja, o resultado é 3.
Ai tem outro porém, o último "1" dessa lista acima se refere à contagem dos zeros. Lembra que eu falei acima que os zeros são representação do falso? Então, esse 1 não pode estar na contagem, por isso aquele "-1" no final da fórmula.
É isso.