CONTAR.SE.S com lógica OR para múltiplos critérios no Excel

Normalmente, pode utilizar a função CONTAR.SE.S para contar células com base numa ou mais condições usando lógica E no Excel. Já enfrentou alguma situação em que precisava contar mais do que um valor a partir de uma única coluna ou intervalo de células? Isso implica aplicar múltiplas condições com lógica OU. Nesses casos, pode combinar as funções SOMA e CONTAR.SE.S ou optar pela função SOMARPRODUTO.

• Contar células com condições OR no Excel
  • Contar células com condições OR utilizando as funções SOMA e CONTAR.SE.S
  • Contar células com condições OR utilizando a função SOMARPRODUTO
• Contar células com múltiplos conjuntos de condições OR no Excel
  • Contar células com dois conjuntos de condições OR utilizando as funções SOMA e CONTAR.SE.S
  • Contar células com múltiplos conjuntos de condições OR utilizando a função SOMARPRODUTO

Contar células com condições OR no Excel

Por exemplo, tenho um intervalo de dados como o apresentado na imagem seguinte e pretendo contar quantos produtos são «Lápis» ou «Régua». Abordarei aqui duas fórmulas para resolver esta tarefa no Excel.

Contar células com condições OR utilizando as funções SOMA e CONTAR.SE.S

No Excel, para contar com múltiplas condições OU, pode utilizar as funções SOMA e CONTAR.SE.S juntamente com uma constante de matriz. A sintaxe genérica é:

• range: O Intervalo de Dados contém os critérios a partir dos quais conta as células;
• critério1, critério2, critério3…: As condições com base nas quais pretende contar as células.

Para contar o número de produtos que sejam «Lápis» ou «Régua», copie ou introduza a fórmula seguinte numa célula vazia e, em seguida, prima a tecla Enterpara obter o resultado:

Explicação da fórmula:

=SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))

• {«Lápis»,«Régua»}: Em primeiro lugar, agrupe todas as condições numa constante de matriz como esta: {«Lápis»,«Régua»}, separando os itens por vírgulas.
• COUNTIFS(B2:B13,{«Pencil»,«Ruler»}): Esta função CONTAR.SE.S devolve as contagens individuais de «Lápis» e «Régua», apresentando o seguinte resultado: {2,3}.
• SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))=SUM({2,3}): Por fim, esta função SOMA adiciona todos os elementos da matriz e devolve o resultado: 5.

Dicas: Também pode utilizar referências de células para os critérios. Aplique a fórmula de matriz seguinte e, em seguida, prima simultaneamente as teclas Ctrl + Shift + Enter para obter o resultado correto:

Contar células com condições OR utilizando a função SOMARPRODUTO

Eis outra fórmula criada com a função SOMARPRODUTO que também pode ajudar a contar células com lógica OR. A sintaxe genérica é:

• range: O Intervalo de Dados contém os critérios a partir dos quais conta as células;
• criterion1, criterion2, criterion3…: As condições com base nas quais pretende contar as células.

Copie ou introduza a fórmula seguinte numa célula vazia e prima a tecla Enterpara obter o resultado:

Explicação da fórmula:

=SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))

• B2:B13={«Lápis»,«Régua»}: Esta expressão compara cada um dos critérios «Lápis» e «Régua» com o intervalo B2:B13. Sempre que um critério é encontrado, devolve VERDADEIRO; caso contrário, devolve FALSO. O resultado obtido será o seguinte: {VERDADEIRO,FALSO;FALSO,FALSO;FALSO,FALSO;FALSO,VERDADEIRO;FALSO,FALSO;VERDADEIRO,FALSO;FALSO,FALSO;FALSO,VERDADEIRO;FALSO,FALSO;FALSO,FALSO;FALSO,VERDADEIRO;FALSO,FALSO}.
• 1*(B2:B13={«Lápis»,«Régua»}): A multiplicação converte os valores lógicos — VERDADEIRO e FALSO — em 1 e 0, obtendo-se assim um resultado como este: {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Por fim, a função SOMARPRODUTO soma todos os números da matriz para obter o resultado: 5.

Contar células com múltiplos conjuntos de condições OR no Excel

Por vezes, poderá precisar de contar células que satisfaçam dois ou mais conjuntos de condições OR. Nesse caso, pode utilizar quer a função SOMA com CONTAR.SE.S e uma constante de matriz, quer a função SOMARPRODUTO em conjunto com as funções ÉNÚMERO e CORRESP.

Contar células com dois conjuntos de condições OR utilizando as funções SOMA e CONTAR.SE.S

Para lidar apenas com dois conjuntos de critérios OU, basta adicionar outra constante de matriz à fórmula CONTAR.SE.S.

Por exemplo, tenho o intervalo de dados apresentado na imagem seguinte e pretendo contar as pessoas que encomendaram «Lápis» ou «Régua» e cujo montante seja 200.

Introduza ou copie a fórmula seguinte numa célula e, em seguida, prima a tecla Enterpara obter o resultado, conforme ilustrado:

: Na fórmula, utilize um ponto e vírgula na segunda constante da matriz para criar uma matriz vertical.

Contar células com múltiplos conjuntos de condições OR utilizando a função SOMARPRODUTO

A fórmula acima só funciona para dois conjuntos de critérios OR. Se precisar de incluir mais critérios, uma fórmula avançada com SOMARPRODUTO, combinada com as funções ÉNÚMERO e CORRESP, pode ser a solução ideal.

Considere os seguintes dados como exemplo: para contar as pessoas que encomendaram «Lápis» ou «Régua», cujo estado seja «Entregue» ou «Em trânsito» e que tenham sido assinadas por «Bob» ou «Eko», deverá utilizar uma fórmula avançada.

Copie ou introduza a fórmula seguinte numa célula vazia e, em seguida, prima a tecla Enterpara obter o cálculo, conforme ilustrado:

Explicação da fórmula:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0)))

ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0)):

• MATCH(B2:B13,{«Pencil»,«Ruler»},0): A função CORRESP compara cada célula do intervalo B2:B13 com os valores da constante de matriz. Sempre que encontra uma correspondência, devolve a posição relativa desse valor na matriz; caso contrário, retorna um erro. Assim, obtém a seguinte matriz: {1;#N/D;#N/D;2;#N/D;1;#N/D;2;1;#N/D;2;#N/D}.
• ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): A função ÉNÚMERO converte os números em VERDADEIRO e os valores de erro em FALSO, obtendo-se: {VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;FALSO}.

A lógica acima também pode ser aplicada às segunda e terceira expressões ÉNÚMERO.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0))):

• Em seguida, estas três matrizes são multiplicadas dentro da função SOMARPRODUTO, que converte automaticamente os valores VERDADEIRO e FALSO em 1 e 0 durante a operação matemática, conforme ilustrado: SOMARPRODUTO({1;0;0;1;0;1;0;1;1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1}*{1;0;0;0;0;0;0;1;0;0;1;0}) = SOMARPRODUTO({1;0;0;0;0;0;0;1;0;0;1;0}).
• Por fim, a função SOMARPRODUTO soma todos os números da matriz para obter o resultado: 3.

Função relacionada utilizada:

Mais artigos: