EXCEL: Cuente valores únicos en un rango con CONTAR.SI

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 SUMAPRODUCTO. En el programa de ejemplo, la fórmula en F6 es:

=SUMAPRODUCTO(1/CONTAR.SI(B5:B14,B5:B14))

Como funciona la fórmula

Trabajando de adentro hacia afuera, CONTAR.SI está configurado para valores en el rango B5: B14, utilizando todos estos mismos valores como criterio:

CONTAR.SI(B5:B14,B5:B14)

Debido a que proporcionamos 10 valores para los criterios, recuperamos 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 un 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 1s, pero los valores que ocurran varias veces aparecerán como valores fraccionales 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 SUMAPRODUCTO 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:

=SUMAPRODUCTO(1/CONTAR.SI(data,data&""))

Al concatenar una cadena vacía ("") a los datos, evitamos que los ceros terminen en la matriz creada por CONTAR.SI 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 usar una cadena vacía ("") para los criterios contará celdas vacías.

Sin embargo, aunque esta versión de la fórmula no arrojará un error # DIV / 0 cuando esté con celdas en blanco, sí incluirá celdas en blanco en el recuento. Si desea excluir celdas en blanco del recuento, use:

=SUMAPRODUCTO((data<>"")/CONTAR.SI(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 se 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.