Cuente valores únicos en Excel usando la función CONTAR.SI

En este tutorial, aprenderá a contar valores únicos en Excel usando fórmulas (funciones CONTAR.SI y SUMPRODUCTO).

Cómo contar valores únicos en Excel

Supongamos que tenemos un conjunto de datos como se muestra a continuación:

Para el propósito de este tutorial, nombraré el rango A2: A10 como NOMBRES. En el futuro, usaremos este rango con nombre en las fórmulas.

Consulte también: Cómo crear rangos con nombre en Excel.

En este conjunto de datos, hay una repetición en el rango de NOMBRES. Para obtener el recuento de nombres únicos de este conjunto de datos (A2: A10), podemos usar una combinación de funciones COUNTIF y SUMPRODUCT como se muestra a continuación:

= SUMPRODUCTO (1 / CONTAR.SI (NOMBRES, NOMBRES))

¿Cómo funciona esta fórmula?

Analicemos esta fórmula para comprender mejor:

• COUNTIF (NOMBRES, NOMBRES)
  • Esta parte de la fórmula devuelve una matriz. En el ejemplo anterior, sería {2; 2; 3; 1; 3; 1; 2; 3; 2}. Los números aquí indican cuántas veces aparece un valor en el rango de celdas dado.
    Por ejemplo, el nombre es Bob, que aparece dos veces en la lista, por lo que devolvería el número 2 para Bob. De manera similar, Steve ocurre tres veces y, por lo tanto, se devuelve 3 para Steve.
• 1 / COUNTIF (NOMBRES, NOMBRES)
  • Esta parte de la fórmula devolvería una matriz: {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
    Como hemos dividido 1 por la matriz, devuelve esta matriz.
    Por ejemplo, el primer elemento de la matriz devuelto anteriormente fue 2. Cuando 1 se divide por 2, devuelve .5.
• SUMPRODUCT (1 / COUNTIF (NOMBRES, NOMBRES))
  • SUMPRODUCT simplemente suma todos estos números. Tenga en cuenta que si Bob aparece dos veces en la lista, la matriz anterior devuelve .5 dondequiera que aparezca el nombre de Bob en la lista. De manera similar, dado que Steve aparece tres veces en la lista, la matriz devuelve .3333333 cada vez que aparece el nombre de Steve. Cuando sumamos los números para cada nombre, siempre devolvería 1. Y si sumamos todos los números, devolvería el recuento total de nombres únicos en la lista.

Esta fórmula funciona bien hasta que no tenga celdas en blanco en el rango. Pero si tiene celdas en blanco, ¡devolvería un # DIV / 0! error.

¿Cómo manejar las celdas en BLANCO?

Primero entendamos por qué devuelve un error cuando hay una celda en blanco en el rango. Supongamos que tenemos el conjunto de datos como se muestra a continuación (con la celda A3 en blanco):

Ahora, si usamos la misma fórmula que usamos anteriormente, la parte CONTAR.SI de la fórmula devuelve una matriz {2; 0; 3; 1; 3; 1; 2; 3; 1}. Como no hay texto en la celda A3, su recuento se devuelve como 0.

Y como estamos dividiendo 1 por toda esta matriz, ¡devuelve un # DIV / 0! error.

Para manejar este error de división en el caso de celdas en blanco, use la siguiente fórmula:

= SUMPRODUCTO ((1 / CONTAR.SI (NOMBRES, NOMBRES & ””)))

Un cambio que hemos realizado en esta fórmula es la parte de criterios de la función CONTAR.SI. Hemos utilizado NAMES & ”” en lugar de NAMES. Al hacer esto, la fórmula devolvería el recuento de celdas en blanco (antes devolvía 0 donde había una celda en blanco).

NOTA: Esta fórmula contaría las celdas en blanco como un valor único y lo devolvería en el resultado.

En el ejemplo anterior, el resultado debería ser 5, pero devuelve 6 ya que la celda en blanco se cuenta como uno de los valores únicos.

Aquí está la fórmula que se encarga de las celdas en blanco y no las cuenta en el resultado final:

= SUMPRODUCTO ((NOMBRES ””) / CONTAR.SI (NOMBRES, NOMBRES & ””))

En esta fórmula, en lugar de 1 como numerador, hemos utilizado NOMBRES ””. Esto devuelve una matriz de VERDADEROS y FALSOS. Devuelve FALSE siempre que haya una celda en blanco. Dado que VERDADERO equivale a 1 y FALSO equivale a 0 en los cálculos, las celdas en blanco no se cuentan ya que el numerador es 0 (FALSO).

Ahora que tenemos listo el esqueleto básico de la fórmula, podemos ir un paso más allá y contar diferentes tipos de datos.

Cómo contar valores únicos en Excel que son texto

Usaremos el mismo concepto discutido anteriormente para crear la fórmula que solo contará los valores de texto que son únicos.

Aquí está la fórmula que contará valores de texto únicos en Excel:

= SUMPRODUCTO ((ISTEXT (NAMES) / COUNTIF (NAMES, NAMES & ””)))

Todo lo que hemos hecho es utilizar la fórmula ISTEXT (NAMES) como numerador. Devuelve VERDADERO cuando la celda contiene texto y FALSO si no lo tiene. No contará las celdas en blanco, pero contará las celdas que tengan una cadena vacía (“”).

Cómo contar valores únicos en Excel que son numéricos

Aquí está la fórmula que contará valores numéricos únicos en Excel

= SUMPRODUCT ((ISNUMBER (NAMES)) / COUNTIF (NAMES, NAMES & ””))

Aquí, estamos usando ISNUMBER (NAMES) como numerador. Devuelve VERDADERO cuando la celda contiene un tipo de datos numérico y FALSO si no es así. No cuenta las celdas en blanco.