Consejos de Excel: cómo contar los elementos de la lista

Recientemente, visité a un amigo que estaba trabajando en una impresión que obviamente fue generada por una aplicación de hoja de cálculo. Era una lista de nombres y direcciones de clientes ordenados por código postal, y mi amigo contaba manualmente cuántos clientes había en cada región de código postal. Odio ver a los usuarios perder el tiempo haciendo algo manualmente que podría hacerse con software.

VER: Google Workspace frente a Microsoft 365: un análisis en paralelo con lista de verificación (TechRepublic Premium)

Naturalmente, tuve que meter la nariz en el proceso y señalar que hay una manera mucho mejor de obtener esos números. En este tutorial, les mostraré lo que les mostré: Cómo usar la función COUNTIF() de Excel para devolver la cantidad de veces que un valor específico, en este caso, los códigos postales, aparece en una lista. En el camino, también aprenderá los conceptos básicos de COUNTIF() para que pueda usar esta función versátil con su propio trabajo. Luego, usaremos SUBTOTAL() para contar al filtrar.

Para esta demostración, estoy usando Microsoft 365 en un sistema Windows 10 de 64 bits, pero también puede usar esta función con versiones anteriores de Excel. Microsoft Excel para la web es compatible con las dos funciones con las que trabajaremos aquí.

Salta a:

argumentos CONTAR.SI

Antes de usar cualquiera de las funciones, veamos los argumentos de CONTAR.SI(). COUNTIF() devuelve el número de celdas que cumplen una condición específica que especifica. En nuestro caso, estamos contando la cantidad de veces que aparece un código postal específico en un rango específico.

COUNTIF() usa la siguiente sintaxis:

COUNTIF(range,criteria)

donde «rango» identifica la lista de valores que está contando y «criterio» expresa la condición para contar.

Antes de continuar, es importante saber que COUNTIF() tiene una limitación. El argumento de criterios está limitado a 255 caracteres cuando se usa un valor de cadena literal. Es dudoso que se encuentre con esta limitación, pero si lo hace, puede concatenar cadenas usando el operador de concatenación & para construir una cadena más larga.

Solución de problemas de CONTAR.SI

Si la función CONTAR.SI() no devuelve nada y sabe que los valores existen, considere las siguientes acciones y consejos:

• Asegúrese de delimitar los valores: Por ejemplo, «manzanas» contará el número de veces que aparece la palabra manzana en el rango de referencia; si omite las comillas, no funcionará. Los valores numéricos no requieren un delimitador, excepto las fechas, que usan el # delimitador
• Compruebe los valores: Su rango de referencia puede tener un carácter de espacio innecesario antes o después de otros caracteres. Use TRIM() para devolver solo los valores que desea.
• Compruebe si su archivo está abierto: Si COUNTIF() se refiere a otro libro de trabajo, ese archivo debe estar abierto. De lo contrario, la función devuelve el #¡VALOR! error.
• Eche un vistazo más de cerca a su texto de criterios: Los valores de los criterios COUNTIF() no distinguen entre mayúsculas y minúsculas. Sin embargo, las comillas tipográficas en los criterios devolverán un error, por lo que si está pegando un valor, tenga cuidado. En general, esto no debería ser un problema.
• No confíe en el formato de celda: COUNTIF() no puede contar celdas en función de los valores de color de fuente o de relleno.

Ahora que está familiarizado con esta función, pongámosla en uso con un ejemplo simple.

Cómo usar la función CONTAR.SI en Excel

Comencemos con un uso simple de COUNTIF(). Como puedes ver en Figura Ala función

=COUNTIF(Table1[ZIPS],10123)

devuelve el valor de 2.

Figura A

Esto se debe a que el valor del código postal, 10123, aparece dos veces en la tabla denominada Table1. Si no está usando un objeto Tabla, use la referencia de rango de la siguiente manera:

=COUNTIF(A2:A21,10123)

Si no está familiarizado con las referencias estructuradas, Table1[ZIPS] podría confundirte. Los datos de ejemplo tienen el formato de un objeto de tabla de Excel. Table1 es el nombre del objeto Table y [ZIPS] es el nombre de la columna.

Especificar un único código postal es fácil, pero es probable que desee ampliar este recuento incluyéndolos todos.

¿Cómo cuento varios elementos en Excel?

Puede especificar un valor literal al usar COUNTIF(), pero el argumento de criterios admite una referencia de celda o rango.

Para demostrar la flexibilidad de esta función, contaremos el número de ocurrencias de cada código postal en los datos de muestra. Por lo general, los códigos postales acompañarán a otros valores de dirección, como el nombre, la dirección, la ciudad y el estado. Mantenemos nuestro ejemplo simple a propósito, porque esos valores son irrelevantes cuando solo cuenta valores de código postal.

VER: Política de uso de servicios de Microsoft 365 (TechRepublic Premium)

Si usa Microsoft 365, use la siguiente expresión para generar una lista única de valores de código postal ordenados (Figura B):

=SORT(UNIQUE(Table1[ZIPS]))

Figura B

SORT() y UNIQUE() son funciones de matriz dinámica, disponibles solo en Excel 365. En nuestro ejemplo, solo hay una expresión, que está en D2. Sin embargo, la expresión se extiende a las celdas de abajo para cumplir con los valores devueltos como una matriz. Si obtiene un error de derrame, hay algo que bloquea la matriz en las celdas debajo de la expresión.

Una vez que tenga una lista única de códigos postales, puede usar COUNTIF() para devolver el recuento de cada valor de código postal, como se muestra en Figura Cusando

=COUNTIF(Table1[ZIPS],D2)

y copiarlo en las celdas restantes.

Figura C

Para obtener más información sobre matrices dinámicas, puede leer Cómo crear una lista única ordenada en una hoja de cálculo de Excel.

¿Cómo cuento varios elementos en Excel pre-365?

Para los usuarios que usan una versión de Excel anterior a Excel 365, tendrán que esforzarse un poco más para obtener los mismos resultados. Si es importante para usted que la lista única de códigos postales esté ordenada, ordene los datos de origen antes de continuar.

Para hacerlo, simplemente puede hacer clic en cualquiera de las celdas de la columna A y hacer clic en el botón Ordenar de forma ascendente en el grupo Ordenar y filtrar en la pestaña Datos. Alternativamente, puede hacer clic en Ordenar y filtrar en el grupo Edición en la pestaña Inicio.

Para crear una lista única de códigos postales a partir de los valores de la columna A, haga lo siguiente:

1. Haga clic en cualquier grupo de celdas del conjunto de datos; en este ejemplo, hemos seleccionado A1:A21.
2. Haga clic en la pestaña Datos y luego haga clic en Avanzado en el grupo Ordenar y filtrar.
3. Haga clic en la opción Copiar a otra ubicación.
4. Excel mostrará $A$1:$A$21 como Rango de lista. Si no hace esto, puede arreglarlo manualmente.
5. Elimine el rango de criterios si hay uno.
6. Haga clic en Copiar a control y luego haga clic en una celda no seleccionada, como G1.
7. Marque la opción Solo registros únicos (Figura D).

Figura D

1. Haga clic en Aceptar.

Esta función también copia el texto del encabezado de A1 y el formato. No hay forma de evitar ninguna de estas copias, pero está bien, porque ninguna interfiere con nuestra tarea.

En este punto, todo lo que queda es la función para contar entradas únicas en la columna A según las entradas en la lista única en la columna G. Ahora es el momento de ingresar la siguiente función en la celda H2:

=COUNTIF(Table1[ZIPS],G2)

Luego lo copiará en las celdas restantes. Como puedes ver en Figura Eesta función devuelve los mismos recuentos que la primera.

Figura E

¿Notaste el 20 en negrita en la celda H9? Esa es una función SUM(), que asegura que el número de entradas contadas sea igual al número de entradas originales. Dado que teníamos 20 entradas en nuestros datos de origen en la columna A, esperaríamos que la cantidad total de entradas únicas contadas fuera la misma.

COUNTIF() es una forma útil de contar valores específicos en una lista, pero también puede encontrarse con situaciones en las que desea contar elementos en una lista filtrada. Veamos cómo hacer eso a continuación.

¿Cómo cuento listas filtradas en Excel?

El uso de COUNTIF() funciona muy bien en muchas situaciones, pero ¿qué pasa si desea un conteo basado en los resultados de una lista filtrada? En esta situación, la función COUNTIF() no funcionará para usted. La función seguirá devolviendo los resultados correctos, pero no devolverá el recuento correcto para el conjunto filtrado. En su lugar, querrá usar la función SUBTOTAL() para contar una lista filtrada.

La función SUBTOTAL() de Excel es bastante especial, ya que se adapta al filtrado. Específicamente, independientemente del cálculo matemático, esta función evalúa solo los valores que llegan a la lista filtrada. Esta función utiliza la siguiente sintaxis:

SUBTOTAL(number,reference)

“Número” identifica el cálculo matemático y “referencia” especifica los valores. De forma predeterminada, el número es 109, que es SUM(). Referirse a Tabla A para obtener una lista completa de valores numéricos:

Tabla A

Incluye filas ocultas	Excluye filas ocultas	Función
1	101	PROMEDIO
2	102	CONTAR
3	103	CONTARA
4	104	MÁX.
5	105	MÍN.
6	106	PRODUCTO
7	107	DESVEST
8	108	DESVEST
9	109	SUMA
10	110	VAR
11	111	VARP

En la última sección, a COUNTIF() no le importaba si los datos de origen eran un rango de datos normal o un objeto de tabla. Para que esta solución funcione, debe trabajar con un objeto Tabla. Para convertir un rango de datos en un objeto de tabla, haga clic en cualquier lugar dentro del rango de datos y presione Ctrl + T y haga clic en Aceptar para confirmar la conversión. Al hacerlo, se muestra automáticamente un menú desplegable de filtros en la celda del encabezado.

Antes de comenzar a filtrar, debemos agregar una fila especial a la Tabla de la siguiente manera:

1. Haga clic en cualquier lugar dentro de la tabla.
2. Haga clic en la pestaña Diseño de tabla contextual.
3. En el grupo Opciones de estilo de tabla, haga clic en el elemento Fila total (Figura F).

Figura F

Como puedes ver en Figura F, esta fila tiene por defecto una función SUBTOTAL() que totaliza los valores por defecto. En este caso, no queremos un total sino un conteo. Para cambiar el argumento de la función SUBTOTAL(), haga clic en A22 y elija Contar de la lista desplegable que se muestra en Figura G.

Figura G

Como puede ver, hay muchas funciones diferentes que puede elegir. Figura H muestra la cuenta, que es 20.

Figura H

El primer argumento de la función SUBTOTAL() original es 109, que representa SUM(). Cuando cambia la función total a Contar, SUBTOTAL() actualiza ese argumento a 103, que representa CONTAR().

Iniciar el proceso de filtrado

Una vez que la fila total esté en su lugar y muestre el conteo, estará listo para comenzar a filtrar. Para comenzar, intente hacer clic en el menú desplegable de filtrado en A1 y haga lo siguiente:

1. Desmarque (Seleccionar todo).
2. Marque la opción 10125 (figura yo).

figura yo

1. Haga clic en Aceptar.

Como puedes ver en Figura Jel conjunto filtrado incluye dos elementos y la función SUBTOTAL() ahora devuelve dos en lugar de 20. Esta función es especial porque, a diferencia de otras funciones, SUBTOTAL() se actualiza cuando aplica un filtro.

Figura J

Intentémoslo de nuevo, solo que esta vez, verifique dos códigos postales (Figura K).

Figura K

Como puedes ver en Figura LSUBTOTAL() devuelve el recuento de ambos valores de código postal, que es 7. SUBTOTAL() es lo suficientemente flexible como para manejar cualquier filtro que aplique mediante la función de filtro avanzado.

Figura L

Recursos adicionales

Ya sea que use COUNTIF() o SUBTOTAL() a través de la fila total de un objeto Table, contar valores es un trabajo fácil. Para aprender más sobre el conteo, este otro tutorial de TechRepublic puede ayudar: Cómo usar la función UNIQUE() para devolver un conteo de valores únicos en Excel.

Lea a continuación: Las 8 mejores alternativas a Microsoft Project (gratis y de pago) (TechRepublic)