Fórmula genérica
=SUMPRODUCT(1/COUNTIF(data,data))
Resumen
Para contar el número de valores únicos en un rango de celdas, puede usar una fórmula basada en las funciones CONTAR.SI y SUMPRODUCTO. En el programa de ejemplo, la fórmula en F6 es:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Explicación
Trabajando desde adentro hacia afuera, COUNTIF se configura a valores en el rango B5: B14, usando todos estos mismos valores como criterio:
COUNTIF(B5:B14,B5:B14)
Debido a que proporcionamos 10 valores para los criterios, obtenemos una matriz con 10 resultados como este:
(3;3;3;2;2;3;3;3;2;2)
Cada número representa un recuento: "Jim" aparece 3 veces, "Sue" aparece 2 veces, y así sucesivamente.
Esta matriz está configurada como divisor con 1 como numerador. Después de la división, obtenemos otra matriz:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Cualquier valor que ocurra en una sola vez en el rango aparecerá como 1, pero los valores que ocurran varias veces aparecerán como valores fraccionarios que corresponden al múltiplo. (es decir, un valor que aparece 4 veces en los datos generará 4 valores = 0,25).
Finalmente, la función SUMPRODUCTO suma todos los valores de la matriz y devuelve el resultado.
Manejo de celdas en blanco
Una forma de manejar celdas en blanco o vacías es ajustar la fórmula de la siguiente manera:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Al concatenar una cadena vacía ("") a los datos, evitamos que los ceros terminen en la matriz creada por COUNTIF cuando hay celdas en blanco en los datos. Esto es importante, porque un cero en el divisor hará que la fórmula arroje un error # DIV / 0. Funciona porque el uso de una cadena vacía ("") para los criterios contará las celdas vacías.
Sin embargo, aunque esta versión de la fórmula no arrojará un error # DIV / 0 cuando tenga celdas en blanco, incluirá celdas en blanco en el recuento. Si desea excluir las celdas en blanco del recuento, use:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Esto tiene el efecto de cancelar el recuento de celdas en blanco haciendo que el numerador sea cero para los recuentos asociados.
¿Rendimiento lento?
Esta es una fórmula genial y elegante, pero calcula mucho más lentamente que las fórmulas que usan FRECUENCIA para contar valores únicos. Para conjuntos de datos más grandes, es posible que desee cambiar a una fórmula basada en la función FRECUENCIA. Aquí hay una fórmula para valores numéricos y otra para valores de texto.
