Na célula B5 tenho uma fórmula:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
Se a coluna A contiver os dados mostrados abaixo, a fórmula retornará a resposta correta 3, pois existem três valores que possuem mais de uma ocorrência (14, 16 e 17). É exatamente isso que quero saber; que existem três valores que aparecem mais de uma vez. Não preciso saber quais são os valores, nem onde eles estão (embora eu tenha algumas informações interessantes para a última parte).
Column A:
12
13
14
14
14
15
16
16
17
17
No entanto, se em qualquer lugar no intervalo A2:A10 houver uma célula em branco, a contagem de valores duplicados será diminuída em 1 (no exemplo acima, a fórmula retornaria 2, quando deveria ser 3). célula é representada por “B”.
Column A:
12
13
14
B
14
15
16
16
17
17
Neste exemplo, 14, 16 e 17 ainda ocorrem mais de uma vez cada; então a fórmula deveria retornar 3, mas retorna 2.
Tenho quase certeza de que isso se deve à primeira parte, =SUMPRODUCT((A2:A10<>"")onde está contando células essencialmente não vazias. É claro que, se houver duas (ou mais) células em branco, ele será incrementado novamente, mas isso também não está certo, porque ainda está omitindo uma duplicata não em branco (se isso faz algum sentido).
Responder1
Nota: sua pergunta se refere a um9intervalo de células, mas você está mostrando10valores nos exemplos, então isso não faz sentido ... embora eu entenda o problema.
A primeira parte da sua fórmula está OK porque essa é a maneira padrão de contar o número de valores diferentes que não estão em branco...... mas quando você subtrai a segunda, COUNTIFvocê também precisa excluir os espaços em branco, então você precisa remover o &"" parte na segunda COUNTIF, ou seja, esta versão
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....mas esta versão é melhor
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
Ambas as fórmulas funcionarão para dados de texto ou numéricos em A2:A10 (ou uma mistura de ambos), mas apenas para valores numéricos (conforme seu exemplo), você também pode usar FREQUENCYfunções como esta
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)