Resumo
Para contar linhas com a lógica OR, você pode usar uma fórmula baseada na função SUMPRODUCT. No exemplo mostrado, a fórmula em G6 é:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
onde grupo (B5: B15), cor1 (C5: C15) e cor2 (D5: D15) são intervalos nomeados.
Explicação
Um dos problemas mais complicados no Excel é contar linhas em um conjunto de dados com "lógica OR". Existem dois cenários básicos: (1) você deseja contar linhas onde um valor em uma coluna é "x" OU "y" (2) você deseja contar linhas onde um valor, "x", existe em uma coluna OU outra .
Neste exemplo, o objetivo é contar as linhas onde group = "a" AND Color1 OR Color2 são "red". Isso significa que estamos trabalhando com o cenário 2 acima.
Com COUNTIFS
Você pode primeiro chegar à função COUNTIFS, que lida com vários critérios nativamente. No entanto, a função COUNTIFS une condições com a lógica AND, portanto, todos os critérios devem ser TRUE para serem incluídos na contagem:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Isso torna o COUNTIFS impraticável, a menos que usemos várias instâncias de COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Tradução: contar linhas em que grupo é "a" e cor1 é "vermelho" + contar linhas onde grupo é "a" e cor2 é "vermelho" - contar linhas onde grupo é "a" e cor1 é "vermelho" e cor2 é " vermelho "(para evitar contagem dupla).
Isso funciona, mas você pode ver que esta é uma fórmula um tanto complicada e redundante.
Com lógica booleana
Uma solução melhor é usar a lógica booleana e processar o resultado com a função SUMPRODUCT. (Se você precisa de uma introdução à álgebra booleana, este vídeo fornece uma introdução.) No exemplo mostrado, a fórmula em G6 é:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
onde grupo (B5: B15), cor1 (C5: C15) e cor2 (D5: D15) são intervalos nomeados.
A primeira parte do problema é testar o grupo = "a", que fazemos assim:
(group="a")
Como o intervalo B5: B15 contém 11 células, esta expressão retorna uma matriz de 11 valores TRUE e FALSE como este:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Cada TRUE representa uma linha onde o grupo é "A".
Em seguida, precisamos verificar o valor "vermelho" na coluna1 ou coluna2. Fazemos isso com duas expressões unidas por adição (+), já que a adição corresponde à lógica OR na álgebra booleana:
(color1="red")+(color2="red")
O Excel avalia automaticamente os valores TRUE e FALSE como 1s e 0s durante qualquer operação matemática, então o resultado da expressão acima é uma matriz como esta:
(2;0;0;1;1;0;1;0;0;0;1)
O primeiro número na matriz é 2, porque tanto Color1 quanto Color2 são "vermelhos" na primeira linha. Pelas razões explicadas abaixo, precisamos nos proteger contra essa situação, verificando se há valores maiores que zero:
((2;0;0;1;1;0;1;0;0;0;1))>0
Agora, temos novamente uma matriz de valores TRUE e FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
A tabela abaixo resume como o Excel avalia a lógica de cores explicada acima:
Neste ponto, temos os resultados do teste Group = "a" em uma matriz:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
E os resultados do teste de "vermelho" na Cor1 ou Cor2 em outra matriz:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
A próxima etapa é reunir essas duas matrizes com "lógica AND". Para fazer isso, usamos a multiplicação (*), uma vez que a multiplicação corresponde à lógica AND na álgebra booleana.
Depois de multiplicar os dois arrays juntos, temos um único array de 1s e 0s, que é entregue diretamente à função SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
A função SUMPRODUCT retorna a soma dos números, 2, como resultado final. Esta é a contagem de linhas onde group = "a" AND Color1 OR Color2 são "red".
Para evitar contagem dupla
Não queremos contar duas vezes as linhas em que Cor1 e Cor2 são "vermelhas". É por isso que verificamos os resultados de (color1 = "red") + (color2 = "red") para valores maiores que zero no código abaixo:
((color1="red")+(color2="red"))>0
Sem essa verificação, o 2 da primeira linha dos dados apareceria na matriz final e faria com que a fórmula retornasse incorretamente 3 como a contagem final.
Opção FILTER
Uma coisa boa sobre a lógica booleana é que ela funciona perfeitamente com as funções mais recentes do Excel, como XLOOKUP e FILTER. Por exemplo, a função FILTER pode usar exatamente a mesma lógica explicada acima para extrair linhas correspondentes:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
O resultado de FILTER são as duas linhas que atendem aos critérios, conforme visto abaixo:
Se você quiser saber mais sobre essas novas funções, temos uma visão geral e um treinamento em vídeo.
