Fórmula genérica
SUMPRODUCT(--(A:A=A1))
Resumo
Prefácio
Esta é uma introdução irritantemente longa, mas o contexto é importante, desculpe!
Se você tentar contar números muito longos (16+ dígitos) em um intervalo com CONT.SE, poderá ver resultados incorretos, devido a um bug em como certas funções lidam com números longos, mesmo quando esses números são armazenados como texto. Considere a tela abaixo. Todas as contagens na coluna D estão incorretas - embora cada número na coluna B seja único, a contagem retornada por COUNTIF sugere que esses números são duplicados.
=COUNTIF(data,B5)
Esse problema está relacionado ao modo como o Excel trata os números. O Excel só pode lidar com 15 dígitos significativos e, se você inserir um número com mais de 15 dígitos no Excel, verá os dígitos finais convertidos silenciosamente em zero. O problema de contagem mencionado acima decorre desse limite.
Normalmente, você pode evitar esse limite inserindo números longos como texto, iniciando o número com aspas simples ('999999999999999999) ou formatando a (s) célula (s) como Texto antes de inserir. Contanto que você não precise realizar operações matemáticas em um número, esta é uma boa solução e permite que você insira números extra longos para coisas como números de cartão de crédito e números de série sem perder nenhum número.
No entanto, se você tentar usar CONT.SE para contar um número com mais de 15 dígitos (mesmo quando armazenado como texto), poderá ver resultados não confiáveis. Isso acontece porque COUNTIF internamente converte o valor longo de volta em um número em algum ponto durante o processamento, disparando o limite de 15 dígitos descrito acima. Sem todos os dígitos presentes, alguns números podem ser contados como duplicatas quando contados com CONT.SE.
Solução
Uma solução é substituir a fórmula CONT.SE por uma fórmula que usa SUM ou SUMPRODUCT. No exemplo mostrado, a fórmula em E5 se parece com isto:
=SUMPRODUCT(--(data=B5))
A fórmula usa o intervalo nomeado "dados" (B5: B9) e gera a contagem correta para cada número com SUMPRODUCT.
Explicação
Primeiro, a expressão dentro de SUMPRODUCT compara todos os valores no intervalo nomeado "dados" com o valor da coluna B na linha atual. Isso resulta em uma matriz de resultados TRUE / FALSE.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Em seguida, o duplo negativo força os valores TRUE / FALSE para valores 1/0.
=SUMPRODUCT((1;0;0;0;0))
Finalmente, SUMPRODUCT simplesmente soma os itens na matriz e retorna o resultado.
Variante de fórmula de matriz
Você também pode usar a função SUM em vez de SUMPRODUCT, mas esta é uma fórmula de matriz e deve ser inserida com control + shift + enter:
(=SUM(--(B:B=B5)))
Outras funções com este problema
Eu não verifiquei isso, mas parece que várias funções têm o mesmo problema, incluindo SOMASE, SOMASE, CONT.SE, CONT.SE, MÉDIA SE e MÉDIA.
