Resumen
Para contar filas con lógica OR, puede usar una fórmula basada en la función SUMPRODUCTO. En el ejemplo que se muestra, la fórmula en G6 es:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
donde grupo (B5: B15), color1 (C5: C15) y color2 (D5: D15) son rangos con nombre.
Explicación
Uno de los problemas más complicados en Excel es contar filas en un conjunto de datos con "lógica OR". Hay dos escenarios básicos: (1) desea contar filas donde un valor en una columna es "x" O "y" (2) desea contar filas donde un valor, "x", existe en una columna U otra .
En este ejemplo, el objetivo es contar las filas donde grupo = "a" Y Color1 O Color2 son "rojos". Esto significa que estamos trabajando con el escenario 2 anterior.
Con COUNTIFS
Al principio, podría utilizar la función CONTAR.SI, que maneja varios criterios de forma nativa. Sin embargo, la función CONTAR.SI une condiciones con la lógica AND, por lo que todos los criterios deben ser VERDADEROS para ser incluidos en el conteo:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Esto hace que COUNTIFS no funcione, a menos que usemos varias instancias de COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Traducción: cuente filas donde el grupo es "a" y color1 es "rojo" + cuente filas donde el grupo es "a" y color2 es "rojo" - cuente filas donde grupo es "a" y color1 es "rojo" y color2 es " rojo "(para evitar la doble contabilización).
Esto funciona, pero puede ver que es una fórmula algo complicada y redundante.
Con lógica booleana
Una mejor solución es usar lógica booleana y procesar el resultado con la función SUMPRODUCT. (Si necesita una introducción al álgebra booleana, este video proporciona una introducción). En el ejemplo que se muestra, la fórmula en G6 es:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
donde grupo (B5: B15), color1 (C5: C15) y color2 (D5: D15) son rangos con nombre.
La primera parte del problema es probar el grupo = "a" que hacemos así:
(group="a")
Debido a que el rango B5: B15 contiene 11 celdas, esta expresión devuelve una matriz de 11 valores VERDADERO y FALSO como este:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Cada VERDADERO representa una fila donde el grupo es "A".
A continuación, debemos verificar el valor "rojo" en la columna1 o en la columna2. Hacemos esto con dos expresiones unidas por la suma (+), ya que la suma se corresponde con la lógica OR en el álgebra booleana:
(color1="red")+(color2="red")
Excel evalúa automáticamente los valores VERDADERO y FALSO como 1 y 0 durante cualquier operación matemática, por lo que el resultado de la expresión anterior es una matriz como esta:
(2;0;0;1;1;0;1;0;0;0;1)
El primer número de la matriz es 2, porque tanto Color1 como Color2 son "rojos" en la primera fila. Por las razones que se explican a continuación, debemos protegernos contra esta situación comprobando valores superiores a cero:
((2;0;0;1;1;0;1;0;0;0;1))>0
Ahora tenemos nuevamente una matriz de valores VERDADERO y FALSO:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
La siguiente tabla resume cómo Excel evalúa la lógica del color explicada anteriormente:
En este punto, tenemos los resultados de probar Group = "a" en una matriz:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Y los resultados de probar "rojo" en Color1 o Color2 en otra matriz:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
El siguiente paso es unir estas dos matrices con "lógica AND". Para hacer esto, usamos la multiplicación (*), ya que la multiplicación corresponde a la lógica Y en el álgebra de Boole.
Después de multiplicar las dos matrices, tenemos una única matriz de 1 y 0, que se envía directamente a la función SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
La función SUMPRODUCTO devuelve la suma de números, 2, como resultado final. Este es el recuento de filas donde grupo = "a" Y Color1 O Color2 son "rojos".
Para evitar la doble contabilización
No queremos contar dos veces las filas en las que Color1 y Color2 son "rojos". Es por eso que verificamos los resultados de (color1 = "red") + (color2 = "red") para valores mayores que cero en el siguiente código:
((color1="red")+(color2="red"))>0
Sin esta verificación, el 2 de la primera fila de los datos aparecería en la matriz final y haría que la fórmula devolviera incorrectamente 3 como el recuento final.
Opción FILTRO
Una cosa buena sobre la lógica booleana es que funciona perfectamente con las funciones más nuevas de Excel, como XLOOKUP y FILTER. Por ejemplo, la función FILTRO puede usar exactamente la misma lógica explicada anteriormente para extraer filas coincidentes:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
El resultado de FILTER son las dos filas que cumplen los criterios, como se ve a continuación:
Si desea obtener más información sobre estas nuevas funciones, tenemos una descripción general y capacitación en video.
