Como uma contagem de valores que têm mais de 1 ocorrência em uma coluna

Question

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 tiver os dados mostrados abaixo, a fórmula retornará a resposta correta de 3, porque há três valores que têm mais de uma ocorrência (14, 16 e 17). Isso é exatamente o que eu quero saber; que existem três valores que aparecem mais de uma vez. Eu não preciso saber quais são os valores, nem onde eles estão (apesar de eu ter um pouco bacana 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 for uma célula em branco, a contagem de valores duplicados será decrementada em 1 (no exemplo acima, a fórmula retornará 2, quando deveria ser 3) Em minha amostra abaixo, a célula em branco é representada por "B".

Column A:
12
13
14
B
14
15
16
16
17
17 

Neste exemplo, 14, 16 e 17 ainda ocorrem cada vez mais de uma vez; então a fórmula deve retornar 3, mas retorna 2.

Tenho quase certeza de que isso se deve à primeira parte =SUMPRODUCT((A2:A10<>"") Onde está contando essencialmente células não vazias. É claro que, se houver duas (ou mais) células em branco, elas serão novamente incrementadas, mas isso também não está certo, porque ainda está omitindo uma duplicata não em branco (se isso fizer algum sentido).

    

Answer 1

Observação: sua pergunta está se referindo a um intervalo de células 9 , mas você está exibindo valores 10 nos exemplos, o que não é um problema. ..embora eu entendi o problema.

A primeira parte da sua fórmula é OK porque essa é a maneira padrão de contar o número de diferentes valores não em branco ...... mas quando você subtrai o segundo COUNTIF você também precisa excluir espaços em branco, então você precisa para remover a parte & "" no segundo 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 texto ou dados numéricos em A2: A10 (ou uma mistura de ambos), mas apenas para valores numéricos (conforme o seu exemplo), você também pode usar a função FREQUENCY como esta

=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)