Este tutorial demuestra cómo utilizar elExc ªel Función COUNTIF y COUNTIFSciones en Excel para contar los datos que cumplen con ciertos criterios.
Descripción general de la función CONTAR.SI
Puede usar la función CONTAR.SI en Excel para contar celdas que contienen un valor específico, contar celdas que son mayores o iguales a un valor, etc.
(Observe cómo aparecen las entradas de la fórmula)
Sintaxis y argumentos de la función COUNTIF:
= CONTAR.SI (rango, criterios)
distancia - El rango de celdas para contar.
Criterios - Los criterios que controlan qué células deben contarse.
¿Qué es la función CONTAR.SI?
La función CONTAR.SI es una de las funciones más antiguas que se utilizan en las hojas de cálculo. En términos simples, es excelente para escanear un rango y decirle cuántas de las celdas cumplen esa condición. Veremos cómo funciona la función con texto, números y fechas; así como algunas de las otras situaciones que puedan surgir.
Ejemplo básico
Comencemos por mirar esta lista de elementos aleatorios. Tenemos algunos números, celdas en blanco y algunas cadenas de texto.
Si desea saber cuántos elementos coinciden exactamente con los criterios, puede especificar lo que desea buscar como segundo argumento. Un ejemplo de esta fórmula podría verse como
= CONTAR.SI (A2: A9, "Apple")
Esta fórmula devolvería el número 3, ya que hay 3 celdas en nuestro rango que cumplen con ese criterio. Alternativamente, podemos usar una referencia de celda en lugar de codificar un valor. Si escribimos "Apple" en la celda G2, podríamos cambiar la fórmula a
= CONTAR.SI (A2: A9, G2)
Cuando se trata de números, es importante distinguir entre números y números que se almacenan como texto. Por lo general, no se ponen comillas alrededor de los números al escribir fórmulas. Entonces, para escribir una fórmula que verifique el número 5, escribirías
= CONTAR.SI (A2: A9, 5)
Finalmente, también podríamos verificar si hay celdas en blanco usando una cadena de longitud cero. Escribiríamos esa fórmula como
= CONTAR.SI (A2: A9, "")
Nota: Esta fórmula contará tanto las celdas que están realmente vacías como las que están en blanco como resultado de una fórmula, como una función SI.
Partidos parciales
La función CONTAR.SI admite el uso de comodines, "*" o "?", En los criterios. Veamos esta lista de deliciosos productos de panadería:
Para encontrar todos los elementos que comienzan con Apple, podríamos escribir “Apple *”. Entonces, para obtener una respuesta de 3, nuestra fórmula en D2 es
= CONTAR.SI (A2: A5, "Apple *")
Nota: La función CONTAR.SI no distingue entre mayúsculas y minúsculas, por lo que también puede escribir "manzana *" si lo desea.
Volviendo a nuestros productos horneados, es posible que también queramos averiguar cuántos pasteles tenemos en nuestra lista. Podemos encontrarlo colocando el comodín al principio de nuestro término de búsqueda y escribiendo
= CONTAR.SI (A2: A5, "* pastel")
Esta fórmula da el resultado de 2.
También podemos usar comodines para buscar celdas con texto. Volvamos a nuestra lista original de datos.
Para contar el número de celdas que tienen al menos algo de texto, sin contar números o celda en blanco, podemos escribir
= CONTAR.SI (A2: A9, "*")
Puede ver que nuestra fórmula devuelve correctamente un resultado de 4.
Operadores de comparación en COUNTIF
Al escribir los criterios hasta ahora, hemos estado insinuando que nuestro operador de comparación es "=". De hecho, podríamos haber escrito esto:
= CONTAR.SI (A2: A9, "= Apple")
Sin embargo, es un carácter adicional para escribir, por lo que generalmente se omite. Sin embargo, esto significa que puede utilizar otros operadores como mayor que, menor que o no igual a. Veamos esta lista de edades registradas:
Si quisiéramos saber cuántos niños tienen al menos 5 años, podemos escribir una comparación de "mayor o igual que" de la siguiente manera:
= CONTAR.SI (A2: A8, "> = 5")
Nota: El operador de comparación siempre se proporciona como una cadena de texto y, por lo tanto, debe estar entre comillas.
Del mismo modo, también puede comprobar si hay elementos que sean inferiores a un valor determinado. Si necesitamos averiguar cuántos son menos de 8, podemos escribir
= CONTAR.SI (A2: A8, "<8")
Esto nos da el resultado deseado de 5. Ahora imaginemos que todos los niños de 6 años van de excursión. ¿Cuántos niños quedarán? Podemos resolver esto usando una comparación "no igual a" como esta:
= CONTAR.SI (A2: A8, "6")
Ahora podemos ver rápidamente que tenemos 6 niños que no tienen 6 años.
En estos ejemplos comparativos hasta ahora, hemos codificado con dificultad los valores que queríamos. También puede utilizar una referencia de celda. El truco consiste en concatenar el operador de comparación con la referencia de celda. Supongamos que colocamos el número 7 en la celda C2 y queremos que nuestra fórmula en D2 muestre cuántos niños tienen menos de 7 años.
Nuestra fórmula en D2 debe verse así:
= CONTAR.SI (A2: A8, "<" y C2)
Nota: Al escribir estas fórmulas, preste especial atención a si necesita poner un elemento entre comillas o fuera. Los operadores siempre están dentro de las citas, las referencias de celda siempre están fuera de las citas. Los números están afuera si está haciendo una coincidencia exacta, pero adentro si está haciendo un operador de comparación.
Trabajando con fechas
Hemos visto cómo se puede dar un texto o un número como criterio, pero ¿qué pasa cuando necesitamos trabajar con fechas? Aquí hay una lista de muestra rápida con la que podemos trabajar:
Para contar cuántas fechas son posteriores al 4 de mayo, debemos ser cautelosos. Las computadoras almacenan fechas como números, por lo que debemos asegurarnos de que la computadora use el número correcto. Si escribiéramos esta fórmula, ¿obtendríamos el resultado correcto?
= CONTAR.SI (A2: A9, "
La respuesta es "posiblemente". Debido a que omitimos el año de nuestro criterio, la computadora asumirá que nos referimos al año actual. Si todas las fechas con las que estamos trabajando son para el año actual, obtendremos la respuesta correcta. Sin embargo, si hay algunas fechas en el futuro, obtendríamos la respuesta incorrecta. Además, una vez que comience el próximo año, esta fórmula devolverá un resultado diferente. Como tal, probablemente debería evitarse esta sintaxis.
Debido a que puede ser difícil escribir las fechas correctamente dentro de una fórmula, es una buena práctica escribir la fecha que desea usar en una celda, y luego puede usar esa referencia de celda dentro de su fórmula COUNTIF. Entonces, escribamos la fecha del 7 de mayo de 2020 en la celda C2, y luego podemos poner nuestra fórmula en C4.
La fórmula en C4 es
= CONTAR.SI (A2: A9, "<" y C2)
Ahora sabemos que el resultado de 7 es correcto y la respuesta no va a cambiar inesperadamente si abrimos esta hoja de cálculo en el futuro.
Antes de salir de esta sección, es común utilizar la función HOY cuando se trabaja con fechas. Podemos usar eso como lo haríamos con una referencia de celda. Por ejemplo, podríamos cambiar la fórmula anterior para que sea esta:
= CONTAR.SI (A2: A9, "<" Y HOY ())
Ahora nuestra fórmula seguirá actualizándose a medida que avanza el tiempo real, y tendremos un recuento de elementos que son menos que hoy.
Múltiples criterios y CONTARIOS
La función COUNTIF original obtuvo una mejora en 2007 cuando salió COUNTIFS. La sintaxis entre los dos es muy similar, y el último le permite proporcionar rangos y criterios adicionales. Puede usar CONTAR.SI fácilmente en cualquier situación en la que exista CONTAR.SI. Es una buena idea saber que existen ambas funciones.
Veamos esta tabla de datos:
Para averiguar cuántas personas hay en los niveles de pago 1 a 2, puede escribir una suma de funciones COUNTIF como esta:
= CONTAR.SI (B2: B7, "> = 1") - CONTAR.SI (B2: B7, "> 2")
Esta fórmula funcionará, ya que está encontrando todo lo que está por encima de 1, pero luego restando la cantidad de registros que están más allá de su punto de corte. Alternativamente, puede usar COUNTIFS de esta manera:
= CONTAR.SI (B2: B7, "> = 1", B2: B7, "<= 2")
Este último es más intuitivo de leer, por lo que es posible que desee utilizar esa ruta. Además, COUNTIFS es más poderoso cuando necesita considerar múltiples columnas. Supongamos que queremos saber cuántas personas hay en la administración y en el nivel de pago 1. No puede hacer eso con solo un CONTAR.SI; deberías escribir
= CONTAR.SI (A2: A7, "Gestión", B2: B7, 1)
Esta fórmula le daría el resultado correcto de 2. Antes de dejar esta sección, consideremos una lógica de tipo O. ¿Y si quisiéramos saber cuántas personas hay en Management o? Debería agregar algunos COUNTIFS juntos, pero hay dos formas de hacerlo. La forma más sencilla es escribirlo así:
= CONTAR.SI (A2: A7, "HR") + CONTAR.SI (A2: A7, "Gestión")
También puede hacer uso de una matriz y escribir esta fórmula de matriz:
= SUMA (CONTAR.SI (A2: A7, {"HR", "Gestión"}))
Nota: Las fórmulas de matriz deben confirmarse usando `Ctrl + Shift + Enter` no solo con` Enter`.
Cómo funcionará esta fórmula es que verá que ha dado una matriz como entrada. Por lo tanto, calculará el resultado para dos funciones COUNTIF diferentes y las almacenará en una matriz. La función SUMA luego agregará todos los resultados en nuestra matriz para hacer una única salida. Por lo tanto, nuestra fórmula se evaluará así:
= SUMA (CONTAR.SI (A2: A7, {"HR", "Gestión"})) = SUMA ({2, 3}) = 5
Contar valores únicos
Ahora que hemos visto cómo usar una matriz con la función CONTAR.SI, podemos dar un paso más para ayudarnos a contar cuántos valores únicos hay en un rango. Primero, veamos nuevamente nuestra lista de departamentos.
= SUMA (1 / CONTAR.SI (A2: A7, A2: A7))
Podemos ver que hay 6 celdas de datos, pero solo hay 3 elementos diferentes. Para que las matemáticas funcionen, necesitamos que cada elemento valga 1 / N, donde N es la cantidad de veces que se repite un elemento. Por ejemplo, si cada FC valiera solo 1/2, cuando los sumaras obtendrás un recuento de 1, por 1 valor único.
Volvamos a nuestro CONTAR.SI, que está diseñado para calcular cuántas veces aparece un artículo en un rango. En D2, escribiremos la fórmula de matriz
= SUMA (1 / CONTAR.SI (A2: A7, A2: A7))
Cómo funcionará esta fórmula, es para cada celda en el rango de A2: A7, verificará cuántas veces aparece. Con nuestra muestra, esto producirá una variedad de
{2, 2, 3, 3, 3, 1}
Luego, convertimos todos esos números en fracciones haciendo alguna división. Ahora nuestra matriz se parece a
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Cuando sumamos todos estos, obtenemos el resultado deseado de 3.
Countif con dos o múltiples condiciones: la función Countifs
Hasta ahora hemos trabajado solo con la función CONTAR.SI. La función CONTAR.SI solo puede manejar un criterio a la vez. Para CONTAR.SI con varios criterios, debe utilizar la función CONTAR.SI. COUNTIFS se comporta exactamente como COUNTIF. Solo agrega criterios adicionales. Echemos un vistazo al siguiente ejemplo.
= CONTAR.SI (B2: B7, "= 130")
COUNTIF & COUNTIFS en Hojas de cálculo de Google
La función COUNTIF & COUNTIFS funciona exactamente igual en Google Sheets que en Excel:
