En la celda B5 tengo una fórmula:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
Si la columna A tiene los datos que se muestran a continuación, la fórmula devuelve la respuesta correcta de 3, porque hay tres valores que tienen más de una aparición (14, 16 y 17). Esto es exactamente lo que quiero saber; que hay tres valores que aparecen más de una vez. No necesito saber cuáles son los valores, ni dónde están (aunque tengo algo de ingenio para la última parte).
Column A:
12
13
14
14
14
15
16
16
17
17
Sin embargo, si en algún lugar del rango de A2:A10 hay una celda en blanco, entonces el recuento de valores duplicados se reduce en 1 (en el ejemplo anterior, la fórmula devolvería 2, cuando debería ser 3). En mi ejemplo a continuación, en blanco La celda está representada por "B".
Column A:
12
13
14
B
14
15
16
16
17
17
En este ejemplo, 14, 16 y 17 siguen apareciendo cada uno más de una vez; entonces la fórmula debería devolver 3, pero devuelve 2.
Estoy bastante seguro de que esto se debe a la primera parte =SUMPRODUCT((A2:A10<>"")donde se cuentan esencialmente celdas no vacías. Por supuesto, si hay dos (o más) celdas en blanco, se vuelve a incrementar, pero esto tampoco es correcto, porque aún se omite un duplicado que no está en blanco (si eso tiene algún sentido).
Respuesta1
Nota: su pregunta se refiere a un9rango de celdas pero estás mostrando10valores en los ejemplos, por lo que no cuadran del todo... aunque entiendo el problema.
La primera parte de tu fórmula está bien porque esa es la forma estándar de contar el número de valores diferentes que no están en blanco... pero cuando restas la segunda COUNTIFtambién necesitas excluir los espacios en blanco, por lo que debes eliminar el &"" parte en la segunda COUNTIF, es decir, esta versión
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....pero esta versión es mejor
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
Ambas fórmulas funcionarán para texto o datos numéricos en A2:A10 (o una combinación de ambos), pero solo para valores numéricos (según su ejemplo), también puede usar FREQUENCYuna función como esta
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)