EXCEL: Cuente valores numéricos únicos con criterios

Para contar valores numéricos únicos en un rango, puede usar una fórmula basada en las funciones FRECUENCIA, SUMA y SI. En el ejemplo que se muestra, los números de los empleados aparecen en el rango B5: B14. La fórmula en G6 es:

=SUMA(--(FRECUENCIA(SI(C5:C14="A",B5:B14),B5:B14)>0))

que devuelve 2, ya que hay 2 identificadores de empleado únicos en el edificio A.

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, a menos que esté utilizando Excel 365.

Cómo funciona esta fórmula

Nota: Antes de Excel 365, Excel no tenía una función dedicada para contar valores únicos. Esta fórmula muestra una forma única de contar valores únicos, siempre que sean numéricos. Si tiene valores de texto o una combinación de texto y números, deberá usar una fórmula más complicada.

La función Excel FRECUENCIA devuelve una distribución de frecuencia, que es una tabla de resumen que contiene la frecuencia de los valores numéricos, organizados en "contenedores". Lo usamos aquí como una forma indirecta de contar valores numéricos únicos. Para aplicar criterios, utilizamos la función SI.

Trabajando de adentro hacia afuera, primero filtramos los valores con la función SI:

SI(C5:C14="A",B5:B14) // filter on building A

El resultado de esta operación es una matriz como esta:

{905;905;905;905;773;773;FALSO;FALSO;FALSO;FALSO}

Note que todos los identificadores en el edificio B ahora son FALSO. Esta matriz se entrega directamente a la función FRECUENCIA como data_array. Para bins_array, proporcionamos los identificadores mismos:

FRECUENCIA({905;905;905;905;773;773;FALSO;FALSO;FALSO;FALSO},{905;905;905;905;773;773;801;963;963;963})

Con esta configuración, FRECUENCIA devuelve la siguiente matriz:

{4;0;0;0;2;0;0;0;0;0;0}

El resultado es un poco críptico, pero el significado es 905 aparece cuatro veces y 773 aparece dos veces. Los valores FALSO se ignoran automáticamente.

FRECUENCIA tiene una característica especial que devuelve automáticamente cero para cualquier número que ya haya aparecido en la matriz de datos, razón por la cual los valores son cero una vez que se ha encontrado un número. Esta es la característica que permite que este enfoque funcione.

A continuación, se prueba que cada uno de estos valores es mayor que cero:

{4;0;0;0;2;0;0;0;0;0;0}>0

El resultado es una matriz como esta:

{VERDADERO;FALSO;FALSO;FALSO;VERDADERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}

Cada VERDADERO en la lista representa un número único en la lista, y solo necesitamos sumar los valores de VERDADERO con SUMAA. Sin embargo, SUMAA no sumará valores lógicos en una matriz, por lo que primero debemos forzar los valores a 1 o cero. Esto se hace con el doble negativo (-). El resultado es una matriz de solo 1 o 0:

{1;0;0;0;1;0;0;0;0;0;0}

Finalmente, SUMA suma estos valores y devuelve el total, que en este caso es 2.

Criterios múltiples

Puede extender la fórmula para manejar múltiples criterios como este:

{=SUMA(--(FRECUENCIA(SI((criteria1)*(criteria2),values),values)>0))}