Fórmula genérica
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Resumo
Um intervalo contém valores duplicados? Se quiser testar um intervalo (ou lista) para duplicatas, você pode fazer isso com uma fórmula que usa CONT.SE junto com SUMPRODUCT.
No exemplo, há uma lista de nomes no intervalo B3: B11. Se quiser testar esta lista para ver se há nomes duplicados, você pode usar:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Explicação
Trabalhando de dentro para fora, CONT.SE primeiro obtém uma contagem de cada valor em B3: B11 no intervalo B3: B11. Como fornecemos um intervalo (array) de células para os critérios, COUNTIF retorna um array de contagens como resultado. No exemplo mostrado, esta matriz se parece com isto:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
O próximo 1 é subtraído, o que produz uma matriz como esta:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Observe que cada 1 na matriz (ou seja, itens que aparecem apenas uma vez) foi convertido para zero.
Em seguida, SUMPRODUCT adiciona os elementos neste array e retorna o resultado, que neste caso é o número 2, que é então testado para um valor> 0.
Sempre que uma lista contém duplicatas, haverá pelo menos dois 1s na matriz somada por SUMPRODUCT, portanto, um resultado final TRUE significa que a lista contém duplicatas.
Tratamento de células em branco
As células vazias no intervalo farão com que a fórmula acima gere resultados incorretos. Para filtrar células em branco ou vazias, você pode usar a seguinte alternativa:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Aqui usamos a lista de expressão lógica "" para forçar todos os valores associados às células em branco para zero.
