Este tutorial te enseñará cómo usar las funciones COUNTIF y COUNTIFS de Excel para contar celdas que cumplen con múltiples condiciones OR, es decir, si una celda contiene X, Y o Z.
Como ya sabes, la función COUNTIF de Excel está diseñada para contar celdas basándose en un solo criterio, mientras que COUNTIFS evalúa múltiples criterios con la lógica AND. Pero, ¿qué sucede si necesitas que tu tarea se base en la lógica OR, donde se pueden cumplir varias condiciones y cualquiera de ellas puede incluirse en el conteo?
Existen varias soluciones para este tipo de tarea, y en este tutorial las abordaremos en detalle. Los ejemplos asumen que tienes un conocimiento básico de la sintaxis y usos generales de ambas funciones. Si no es así, te recomendaría que empieces revisando lo básico:
Función COUNTIF de Excel – cuenta celdas con un solo criterio.
Función COUNTIFS de Excel – cuenta celdas con múltiples criterios AND.
Ahora que todos estamos en sintonía, ¡vamos al grano!
Contar celdas con condiciones OR en Excel
En esta sección, vamos a ver el escenario más sencillo: contar celdas que cumplen con al menos una de las condiciones especificadas.
Fórmula 1. COUNTIF + COUNTIF
La forma más fácil de contar celdas que tienen un valor u otro (COUNTIF a o b) es escribir una fórmula COUNTIF normal para contar cada elemento por separado y luego sumar los resultados:
COUNTIF(rango, criterio1) + COUNTIF(rango, criterio2)
Por ejemplo, para saber cuántas celdas en la columna A contienen «manzanas» o «bananas»:
=COUNTIF(A:A, "manzanas") + COUNTIF(A:A, "bananas")
En hojas de cálculo reales, es recomendable trabajar con rangos en lugar de columnas completas para que la fórmula funcione más rápido. Para evitar tener que actualizar tu fórmula cada vez que cambian las condiciones, escribe los elementos de interés en celdas predefinidas, digamos F1 y G1, y referencia esas celdas. Por ejemplo:
=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)
Esta técnica funciona bien para un par de criterios, pero si agregas tres o más funciones COUNTIF, la fórmula se vuelve demasiado complicada. En este caso, es mejor optar por una de las siguientes alternativas.
Fórmula 2. COUNTIF con constante de matriz
Aquí tienes una versión más compacta de la fórmula SUMIF con condiciones OR en Excel:
SUM(COUNTIF(rango, {criterio1, criterio2, criterio3, …}))
La fórmula se construye de la siguiente manera:
Primero, agrupas todas las condiciones en una constante de matriz: elementos individuales separados por comas y la matriz encerrada entre llaves como {«manzanas», «bananas», «limones»}.
Luego, incluyes la constante de matriz en el argumento de criterios de una fórmula COUNTIF normal: COUNTIF(A2:A10, {"manzanas","bananas","limones"}).
Finalmente, envuelves la fórmula COUNTIF en la función SUM. Esto es necesario porque COUNTIF devolverá 3 conteos individuales para «manzanas», «bananas» y «limones», y necesitas sumar esos conteos.
Nuestra fórmula completa quedaría así:
=SUM(COUNTIF(A2:A10,{"manzanas","bananas","limones"}))
Si prefieres proporcionar tus criterios como referencias de rango, necesitarás ingresar la fórmula con Ctrl + Shift + Enter para convertirla en una fórmula de matriz. Por ejemplo:
=SUM(COUNTIF(A2:A10,F1:H1))
Observa las llaves en la captura de pantalla a continuación; son la indicación más evidente de una fórmula de matriz en Excel:
Fórmula 3. SUMPRODUCT
Otra forma de contar celdas con lógica OR en Excel es usar la función SUMPRODUCT de esta manera:
SUMPRODUCT(1*(rango ={criterio1, criterio2, criterio3, …}))
Para visualizar mejor la lógica, esto también podría escribirse como:
SUMPRODUCT((rango=criterio1) + (rango=criterio2) + …)
La fórmula prueba cada celda en el rango contra cada criterio y devuelve TRUE si se cumple el criterio, y FALSE en caso contrario. Como resultado intermedio, obtendrás varios arreglos de valores TRUE y FALSE (el número de arreglos es igual al número de criterios). Luego, se suman los elementos de los arreglos en la misma posición, es decir, los primeros elementos de todos los arreglos, los segundos elementos, y así sucesivamente. La operación de suma convierte los valores lógicos en números, así que terminas con un arreglo de 1’s (cuando se cumple uno de los criterios) y 0’s (cuando no se cumple ninguno). Dado que todos los criterios se prueban contra las mismas celdas, no hay forma de que aparezca ningún otro número en el arreglo resultante; solo un arreglo inicial puede tener TRUE en una posición específica, los demás tendrán FALSE. Finalmente, SUMPRODUCT suma los elementos del arreglo resultante y obtienes el conteo deseado.
La primera fórmula funciona de manera similar, con la diferencia de que devuelve un arreglo 2-dimensional de valores TRUE y FALSE, que multiplicas por 1 para convertir los valores lógicos a 1 y 0, respectivamente.
Aplicado a nuestro conjunto de datos de ejemplo, las fórmulas se verían así:
=SUMPRODUCT(1*(A2:A10={"manzanas","bananas","limones"}))
O
=SUMPRODUCT((A2:A10="manzanas") + (A2:A10="bananas") + (A2:A10="limones"))
Si reemplazas la constante de matriz codificada por una referencia de rango, obtendrás una solución aún más elegante:
=SUMPRODUCT(1*(A2:A10=F1:H1))
Nota: La función SUMPRODUCT es más lenta que COUNTIF, por lo que es mejor usar esta fórmula en conjuntos de datos relativamente pequeños.
Contar celdas con lógica OR y AND
Cuando trabajas con grandes conjuntos de datos que tienen relaciones multi-nivel y cruzadas entre elementos, es probable que necesites contar celdas que tengan condiciones OR y AND al mismo tiempo.
Por ejemplo, contemos «manzanas», «bananas» y «limones» que están «entregados». ¿Cómo lo hacemos? Primero, traduzcamos nuestras condiciones al lenguaje de Excel:
- Columna A: «manzanas» o «bananas» o «limones»
- Columna C: «entregados»
Visto de otra manera, necesitamos contar las filas con «manzanas y entregados» O «bananas y entregados» O «limones y entregados». De esta forma, la tarea se reduce a contar celdas con 3 condiciones OR, exactamente como hicimos en la sección anterior. La única diferencia es que usarás COUNTIFS en lugar de COUNTIF para evaluar la condición AND dentro de cada condición OR.
Fórmula 1. COUNTIFS + COUNTIFS
Esta es la fórmula más larga, pero la más fácil de escribir 🙂
=COUNTIFS(A2:A10, "manzanas", C2:C10, "entregados") + COUNTIFS(A2:A10, "bananas", C2:C10, "entregados") + COUNTIFS(A2:A10, "limones", C2:C10, "entregados")
La captura de pantalla a continuación muestra la misma fórmula con referencias a celdas:
=COUNTIFS(A2:A10, K1, C2:C10, K2) + COUNTIFS(A2:A10, L1, C2:C10, K2) + COUNTIFS(A2:A10, M1,C2:C10, K2)
Fórmula 2. COUNTIFS con constante de matriz
Una fórmula COUNTIFS más compacta con lógica AND/OR se puede crear agrupando criterios OR en una constante de matriz:
=SUM(COUNTIFS(A2:A10, {"manzanas","bananas","limones"}, C2:C10, "entregados"))
Cuando usas una referencia de rango para los criterios, necesitarás una fórmula de matriz, completada presionando Ctrl + Shift + Enter:
=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))
Consejo: Si lo necesitas, puedes usar wildcards en los criterios de cualquiera de las fórmulas discutidas anteriormente. Por ejemplo, para contar todo tipo de bananas como «bananas verdes» o «bananas goldfinger», puedes usar esta fórmula:
=SUM(COUNTIFS(A2:A10, {"manzanas","*bananas*","limones"}, C2:C10, "entregados"))
De manera similar, puedes construir una fórmula para contar celdas basadas en otros tipos de criterios. Por ejemplo, para contar «manzanas» o «bananas» o «limones» que están «entregados» y la cantidad es mayor a 200, agrega un par de rango de criterios/critérios más a COUNTIFS:
=SUM(COUNTIFS(A2:A10, {"manzanas","*bananas*","limones"}, C2:C10, "entregados", B2:B10, ">200"))
O usa esta fórmula de matriz (ingresada a través de Ctrl + Shift + Enter):
=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2, B2:B10, ">"&F3))
Contar celdas con múltiples condiciones OR
En el ejemplo anterior, aprendiste cómo probar un conjunto de condiciones OR. Pero, ¿qué pasa si tienes dos o más conjuntos y buscas obtener un total de todas las posibles relaciones OR?
Dependiendo de cuántas condiciones necesites manejar, puedes usar COUNTIFS con una constante de matriz o SUMPRODUCT con ISNUMBER MATCH. La primera es relativamente fácil de construir, pero está limitada a solo 2 conjuntos de condiciones OR. La segunda puede evaluar cualquier número de condiciones (un número razonable, por supuesto, dado el límite de Excel de 255 argumentos y 8192 caracteres para la longitud total de la fórmula), pero puede requerir un poco de esfuerzo para entender la lógica de la fórmula.
Contar celdas con 2 conjuntos de condiciones OR
Cuando se trata solo de dos conjuntos de criterios OR, simplemente agrega una constante de matriz más a la fórmula COUNTIFS discutida anteriormente.
Para que la fórmula funcione, se necesita un cambio crítico pero breve: usa un arreglo horizontal (elementos separados por comas) para un conjunto de criterios y un arreglo vertical (elementos separados por punto y coma) para el otro. Esto le indica a Excel que «empareje» o «calcule cruzadamente» los elementos en los dos arreglos y devuelva un arreglo bidimensional de los resultados.
Por ejemplo, contemos «manzanas», «bananas» o «limones» que están ya sea «entregados» o «en tránsito»:
=SUM(COUNTIFS(A2:A10, {"manzanas", "bananas", "limones"}, B2:B10, {"entregados"; "en tránsito"}))
Ten en cuenta el punto y coma en la segunda constante de matriz:
Debido a que Excel es un programa bidimensional, no es posible construir un arreglo tridimensional o cuadrimensional, por lo que esta fórmula solo funciona para dos conjuntos de criterios OR. Para contar con más criterios, tendrás que cambiar a una fórmula más compleja de SUMPRODUCT que se explicará en el siguiente ejemplo.
Contar celdas con múltiples conjuntos de condiciones OR
Para contar celdas con más de dos conjuntos de criterios OR, usa la función SUMPRODUCT junto con ISNUMBER MATCH.
Por ejemplo, contemos «manzanas», «bananas» o «limones» que están ya sea «entregados» o «en tránsito» y están empaquetados en «bolsa» o «bandeja»:
=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"manzanas","bananas","limones"},0))*
ISNUMBER(MATCH(B2:B10,{"bolsa","bandeja"},0))*
ISNUMBER(MATCH(C2:C10,{"entregados","en tránsito"},0)))
En el corazón de la fórmula, la función MATCH verifica los criterios comparando cada celda en el rango especificado con la constante de matriz correspondiente. Si se encuentra una coincidencia, devuelve la posición relativa del valor en la matriz, N/A de lo contrario. ISNUMBER convierte estos valores en TRUE y FALSE, que equivalen a 1 y 0, respectivamente. SUMPRODUCT se encarga de lo demás y multiplica los elementos de los arreglos. Debido a que multiplicar por cero da cero, solo las celdas que tienen 1 en todos los arreglos sobreviven y se suman.
Así es como utilizas las funciones COUNTIF y COUNTIFS en Excel para contar celdas con múltiples condiciones AND y OR. Si deseas ver más de cerca las fórmulas discutidas en este tutorial, puedes descargar nuestro libro de trabajo de ejemplo a continuación.
Libro de trabajo de práctica
Ejemplos de Excel COUNTIF con condiciones OR (.xlsx)