=CONTARA(UNICOS(A2:A9)) cuenta cuántos valores distintos hay en A2:A9. UNICOS devuelve cada valor una vez y CONTARA cuenta esa lista. Necesita Excel 2021 o Microsoft 365; las versiones anteriores se explican más abajo. En inglés las funciones se llaman UNIQUE y COUNTA, y la tabla muestra la fórmula así: =COUNTA(UNIQUE(A2:A9)). La tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=CONTARA(UNICOS(A2:A9))Ocho pedidos vinieron de cinco clientes. C2 desborda la lista de nombres de UNICOS para que veas qué se está contando, y D2 la cuenta sin necesitar la lista en la hoja. Cambia A9 a Ana y el conteo baja a 4; escribe un nombre nuevo y sube.
UNICOS no distingue mayúsculas, así que Ana y ana cuentan como un solo cliente.
Contar valores únicos en versiones anteriores de Excel
Excel 2019 y las versiones anteriores no tienen UNICOS. La fórmula clásica es:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
En Excel en español: =SUMAPRODUCTO(1/CONTAR.SI(A2:A9;A2:A9)).
CONTAR.SI con todo el rango como criterio devuelve, para cada fila, cuántas veces aparece el valor de esa fila. Un nombre que aparece 3 veces recibe un 3 en cada una de sus filas, así que 1/3 se suma tres veces y el nombre suma exactamente 1. La columna B muestra el conteo de cada fila y la columna C la fracción.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
=SUMAPRODUCTO(1/CONTAR.SI(A2:A9;A2:A9))Las tres filas de Ana suman 0,33 cada una, las dos de Ben 0,50 cada una, y los tres nombres que aparecen una vez suman 1 cada uno: 5 en total, lo mismo que la SUMA de la columna auxiliar. Con decenas de miles de filas esta fórmula es lenta, porque CONTAR.SI recorre todo el rango una vez por fila; UNICOS no tiene ese costo.
Distintos o únicos: valores que aparecen una sola vez
"Único" se usa para dos conteos diferentes. El de arriba cuenta valores distintos: cada nombre una vez. El otro cuenta los valores que aparecen exactamente una vez, como los clientes que pidieron una sola vez. UNICOS lo hace con su tercer argumento, exactly_once, en VERDADERO.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=CONTARA(UNICOS(A2:A9;;VERDADERO))Cinco clientes distintos, pero solo tres de ellos, Cara, Dan y Eva, pidieron una vez. La versión para Excel anterior cuenta las filas cuyo CONTAR.SI es exactamente 1. Si todos los valores se repiten, UNICOS con exactly_once devuelve #CALC! y CONTARA cuenta ese error como 1; la versión con SUMAPRODUCTO da 0.
Contar valores únicos con una condición
Para contar los clientes distintos de una región, filtra primero las filas y luego cuenta lo que queda. FILTRAR conserva las filas de North y UNICOS quita las repeticiones.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
=CONTARA(UNICOS(FILTRAR(A2:A9;B2:B9=D2)))North tiene cinco pedidos de tres clientes: Ana, Cara y Ben. E3 cuenta South de la misma forma. E4 es la versión para Excel 2019 y anteriores: CONTAR.SI.CONJUNTO cuenta cada par de cliente y región, y la condición deja solo las fracciones de North.
Si ninguna fila coincide, FILTRAR devuelve #CALC!, y CONTARA cuenta ese error como un valor: escribe West en D2 y E2 muestra 1, no 0. Envolver la fórmula en SI.ERROR no ayuda, porque CONTARA no devuelve ningún error. Cuenta en su lugar las filas del resultado, que sí transmite el error: =SI.ERROR(FILAS(UNICOS(FILTRAR(A2:A9;B2:B9="West")));0) devuelve 0.
Contar valores únicos sin contar las celdas vacías
Una celda vacía del rango se convierte en un "valor" más. UNICOS la devuelve como un 0 y CONTARA cuenta ese 0, así que para Ana, una celda vacía, Ben, Ana, una celda vacía, Cara y Ben, Excel da:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
En Excel en español: =CONTARA(UNICOS(A2:A8)).
En la fórmula antigua una fila vacía hace que CONTAR.SI devuelva 0, así que 1/0 da #¡DIV/0! (#DIV/0! en inglés; la tabla muestra los nombres de error en inglés). Quita antes las celdas vacías:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
=CONTARA(UNICOS(FILTRAR(A2:A8;A2:A8<>"")))Las dos fórmulas cuentan los tres clientes. FILTRAR con A2:A8<>"" quita las celdas vacías antes de que UNICOS las vea. En la fórmula antigua, A2:A8&"" convierte cada celda vacía en una cadena vacía para que CONTAR.SI nunca devuelva 0, y (A2:A8<>"") da a esas filas un peso de 0.
Práctica: contar los productos
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
Tu turno: Cuenta cuántos productos distintos aparecen en B2:B9. Escribe la fórmula en E2.
Qué fórmula usar según tu Excel
| Conteo | Excel 365 / 2021 | Excel 2019 y anteriores |
|---|---|---|
| Valores distintos | =CONTARA(UNICOS(A2:A9)) | =SUMAPRODUCTO(1/CONTAR.SI(A2:A9;A2:A9)) |
| Valores que aparecen una vez | =CONTARA(UNICOS(A2:A9;;VERDADERO)) | =SUMAPRODUCTO(--(CONTAR.SI(A2:A9;A2:A9)=1)) |
| Distintos, con una condición | =CONTARA(UNICOS(FILTRAR(A2:A9;B2:B9="North"))) | =SUMAPRODUCTO((B2:B9="North")/CONTAR.SI.CONJUNTO(A2:A9;A2:A9;B2:B9;B2:B9)) |
| Distintos, sin celdas vacías | =CONTARA(UNICOS(FILTRAR(A2:A9;A2:A9<>""))) | =SUMAPRODUCTO((A2:A9<>"")/CONTAR.SI(A2:A9;A2:A9&"")) |
En una tabla dinámica, el resumen Recuento distinto hace el mismo trabajo sin fórmula, pero solo cuando la tabla dinámica se crea con la casilla "Agregar estos datos al Modelo de datos" marcada. Para borrar las repeticiones en lugar de contarlas, consulta quitar duplicados.
Preguntas frecuentes
¿Cómo cuento los valores únicos en Excel?
En Excel 365 o 2021, usa =CONTARA(UNICOS(A2:A9)): UNICOS lista cada valor una vez y CONTARA cuenta la lista. En versiones anteriores usa =SUMAPRODUCTO(1/CONTAR.SI(A2:A9;A2:A9)).
¿Cómo cuento los valores que aparecen una sola vez?
Pon el tercer argumento de UNICOS, exactly_once, en VERDADERO: =CONTARA(UNICOS(A2:A9;;VERDADERO)). Para Ana, Ana, Ben da 1, porque solo Ben aparece una vez. En Excel 2019 y anteriores usa =SUMAPRODUCTO(--(CONTAR.SI(A2:A9;A2:A9)=1)).
¿Cómo cuento valores únicos con una condición?
Filtra primero y cuenta después: =CONTARA(UNICOS(FILTRAR(A2:A9;B2:B9="North"))) cuenta los clientes distintos de las filas de North. Si ninguna fila coincide, CONTARA cuenta el error #CALC! de FILTRAR como 1, así que cuando eso pueda pasar usa =SI.ERROR(FILAS(UNICOS(FILTRAR(A2:A9;B2:B9="North")));0).
¿Cómo cuento valores únicos sin contar las celdas vacías?
Quita las celdas vacías antes de UNICOS: =CONTARA(UNICOS(FILTRAR(A2:A9;A2:A9<>""))). En versiones anteriores de Excel, =SUMAPRODUCTO((A2:A9<>"")/CONTAR.SI(A2:A9;A2:A9&"")) las salta.
