El tutorial explica cómo usar las funciones CONTAR.SI y CONTAR.SI de Excel para contar celdas con múltiples condiciones OR y AND.
Como todos saben, la función CONTAR.SI de Excel está diseñada para contar celdas según un solo criterio, mientras que CONTAR.SI evalúa varios criterios con la lógica AND. Pero, ¿qué sucede si su tarea requiere lógica OR? Cuando se proporcionan varias condiciones, ¿cualquiera puede coincidir para ser incluida en el conteo?
Hay algunas soluciones posibles para esta tarea, y este tutorial las cubrirá todas en detalle. Los ejemplos implican que tiene un conocimiento sólido de la sintaxis y los usos generales de ambas funciones. Si no es así, es posible que desee comenzar con la revisión de los conceptos básicos:
Función CONTAR.SI de Excel: cuenta las celdas con un criterio.
Función COUNTIFS de Excel: cuenta las celdas con múltiples criterios AND.
Ahora que todos están en la misma página, profundicemos:
Contar celdas con condiciones OR en Excel
Esta sección cubre el escenario más simple: contar celdas que cumplan cualquiera (al menos una) de las condiciones especificadas.
Fórmula 1. CONTAR.SI + CONTAR.SI
La forma más fácil de contar celdas que tienen un valor u otro (Countif a o b) es escribir una fórmula regular COUNTIF para contar cada elemento individualmente y luego sumar los resultados:
CONTAR.SI(rango, criterio1) + CONTAR.SI(rango, criterio2)
Como ejemplo, averigüemos cuántas celdas en la columna A contienen «manzanas» o «plátanos»:
=COUNTIF(A:A, "apples") + COUNTIF(A:A, "bananas")
En las hojas de trabajo de la vida real, es una buena práctica operar en rangos en lugar de columnas enteras para que la fórmula funcione más rápido. Para ahorrarse la molestia de actualizar su fórmula cada vez que cambien las condiciones, escriba los elementos de interés en celdas predefinidas, digamos F1 y G1, y haga referencia a esas celdas. Por ejemplo:
=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)
Esta técnica funciona bien para un par de criterios, pero agregar tres o más funciones COUNTIF juntas haría que la fórmula fuera demasiado engorrosa. En este caso, será mejor que te quedes con una de las siguientes alternativas.
Fórmula 2. CONTAR.SI con matriz constante
Aquí hay una versión más compacta de la fórmula SUMIF con condiciones OR en Excel:
SUMA(CONTAR.SI(rango, {criterio1, criterio2, criterio3, …}))
La fórmula se construye de esta manera:
Primero, empaqueta todas las condiciones en una constante de matriz: elementos individuales separados por comas y la matriz encerrada entre llaves como {«manzanas», «plátanos», «limones»}.
Luego, incluye la constante de matriz en el argumento de criterios de una fórmula CONTAR.SI normal: CONTAR.SI(A2:A10, {«manzanas»,»plátanos»,»limones»})
Finalmente, deforme la fórmula CONTAR.SI en la función SUMA. Es necesario porque COUNTIF devolverá 3 recuentos individuales para «manzanas», «plátanos» y «limones», y debe sumar esos recuentos.
Nuestra fórmula completa es la siguiente:
=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))
Si prefiere proporcionar sus criterios como referencias de rango, deberá ingresar la fórmula con Ctrl + Shift + Enter para convertirla en una fórmula de matriz. Por ejemplo:
=SUM(COUNTIF(A2:A10,F1:H1))
Observe las llaves en la captura de pantalla a continuación: es la indicación más evidente de una fórmula de matriz en Excel:
Fórmula 3. SUMAPRODUCTO
Otra forma de contar celdas con lógica OR en Excel es usar la función SUMPRODUCT de esta manera:
SUMAPRODUCTO(1*(rango ={criterio1, criterio2, criterio3, …}))
Para visualizar mejor la lógica, esto también podría escribirse como:
SUMAPRODUCTO((rango=criterio1) + (rango=criterio2) + …)
La fórmula prueba cada celda en el rango contra cada criterio y devuelve VERDADERO si se cumple el criterio, FALSO de lo contrario. Como resultado intermedio, obtiene algunas matrices de valores VERDADERO y FALSO (la cantidad de matrices es igual a la cantidad de sus criterios). Luego, los elementos de la matriz en la misma posición se suman, es decir, los primeros elementos de todas las matrices, los segundos elementos, y así sucesivamente. La operación de suma convierte los valores lógicos en números, por lo que termina con una matriz de 1 (uno de los criterios coincide) y 0 (ninguno de los criterios coincide). Debido a que todos los criterios se prueban con las mismas celdas, no hay forma de que aparezca ningún otro número en la matriz resultante: solo una matriz inicial puede tener VERDADERO en una posición específica, otras tendrán FALSO. Finalmente, SUMPRODUCT suma los elementos de la matriz resultante y obtiene el conteo deseado.
La primera fórmula funciona de manera similar, con la diferencia de que devuelve una matriz bidimensional de valores VERDADERO y FALSO, que se multiplican por 1 para convertir los valores lógicos en 1 y 0, respectivamente.
Aplicadas a nuestro conjunto de datos de muestra, las fórmulas toman la siguiente forma:
=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))
O
=SUMPRODUCT((A2:A10="apples") + (A2:A10="bananas") + (A2:A10="lemons"))
Reemplace la constante de matriz codificada con una referencia de rango y obtendrá 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 esta fórmula es mejor para usar en conjuntos de datos relativamente pequeños.
Contar celdas con lógica OR y AND
Cuando se trabaja con grandes conjuntos de datos que tienen relaciones multinivel y entre niveles entre elementos, es probable que necesite contar celdas con condiciones OR y AND a la vez.
Como ejemplo, hagamos un recuento de «manzanas», «plátanos» y «limones» que se «entregan». ¿Como hacemos eso? Para empezar, traduzcamos nuestras condiciones al lenguaje de Excel:
- Columna A: «manzanas» o «plátanos» o «limones»
- Columna C: «entregado»
Mirando desde otro ángulo, necesitamos contar filas con «manzanas y entregadas» O «plátanos y entregadas» O «limones y entregadas». Dicho de esta manera, la tarea se reduce a contar celdas con 3 condiciones OR, ¡exactamente lo que hicimos en la sección anterior! La única diferencia es que utilizará COUNTIFS en lugar de COUNTIF para evaluar el criterio AND dentro de cada condición OR.
Fórmula 1. CONTADORES + CONTADORES
Es la fórmula más larga, que es la más fácil de escribir 🙂
=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") + COUNTIFS(A2:A10, "bananas", C2:C10, "delivered")) + COUNTIFS(A2:A10, "lemons", C2:C10, "delivered"))
La siguiente captura de pantalla muestra la misma fórmula con referencias de 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 matriz constante
Se puede crear una fórmula COUNTIFS más compacta con lógica AND/OR empaquetando criterios OR en una constante de matriz:
=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))
Al usar una referencia de rango para los criterios, necesita una fórmula de matriz, que se completa presionando Ctrl + Shift + Enter:
=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))
Consejo. Si es necesario, usted es libre de usar comodines en los criterios de cualquiera de las fórmulas discutidas anteriormente. Por ejemplo, para contar todo tipo de plátanos, como «plátanos verdes» o «plátanos dorados», puede usar esta fórmula:
=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered"))
De manera similar, puede crear una fórmula para contar celdas según otros tipos de criterios. Por ejemplo, para obtener un recuento de «manzanas», «plátanos» o «limones» que se «entregan» y la cantidad es superior a 200, agregue un par de criterios/rango de criterios más a COUNTIFS:
=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered", B2:B10, ">200"))
O use 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, ha aprendido a probar un conjunto de condiciones OR. Pero, ¿qué sucede si tiene dos o más conjuntos y desea obtener un total de todas las relaciones OR posibles?
Dependiendo de cuántas condiciones necesite manejar, puede usar COUNTIFS con una constante de matriz o SUMPRODUCT con ISNUMBER MATCH. El primero es relativamente fácil de construir, pero está limitado a solo 2 conjuntos de condiciones OR. Este último puede evaluar cualquier cantidad de condiciones (una cantidad 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 comprender la lógica de la fórmula.
Cuente las celdas con 2 conjuntos de condiciones OR
Cuando se trata de solo dos conjuntos de criterios OR, solo agregue una constante de matriz más a la fórmula COUNTIFS discutida anteriormente.
Para que la fórmula funcione, se necesita un minuto pero un cambio crítico: use un matriz horizontal (elementos separados por comas) para un conjunto de criterios y matriz vertical (elementos separados por punto y coma) por el otro. Esto le dice a Excel que «empareje» o «calcule de forma cruzada» los elementos en las dos matrices y devuelva una matriz bidimensional de los resultados.
Como ejemplo, contemos «manzanas», «plátanos» o «limones» que están «entregados» o «en tránsito»:
=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))
Tenga en cuenta el punto y coma en la segunda constante de matriz:
Debido a que Excel es un programa de 2 dimensiones, no es posible construir una matriz de 3 o 4 dimensiones y, por lo tanto, esta fórmula solo funciona para dos conjuntos de criterios OR. Para contar con más criterios, deberá cambiar a una fórmula SUMAPRODUCTO más compleja que se explica 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, use la función SUMPRODUCT junto con ISNUMBER COINCIDIR.
Por ejemplo, obtengamos un recuento de «manzanas», «plátanos» o «limones» que se «entregan» o «en tránsito» y se empaquetan en «bolsas» o «bandejas»:
=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*
ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*
ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))
En el corazón de la fórmula, la función COINCIDIR verifica los criterios comparando cada celda en el rango especificado con la constante de matriz correspondiente. Si se encuentra la coincidencia, devuelve una posición relativa del valor si la matriz, N/A de lo contrario. ESNUMERO convierte estos valores en VERDADERO y FALSO, que equivalen a 1 y 0, respectivamente. SUMPRODUCT lo toma desde allí y multiplica los elementos de las matrices. Debido a que multiplicar por cero da cero, solo las celdas que tienen 1 en todas las matrices sobreviven y se suman.
La siguiente captura de pantalla muestra el resultado:
Así es como usa las funciones CONTAR.SI y CONTAR.SI en Excel para contar celdas con múltiples condiciones AND y OR. Para ver más de cerca las fórmulas discutidas en este tutorial, puede descargar nuestra muestra Excel CONTAR SI O/Y libro de trabajo. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!