Countifs com lógica OR para vários critérios no Excel
Normalmente, você pode usar a função COUNTIFS para contar células com base em uma ou mais condições com a lógica AND no Excel. Você já passou por uma situação em que precisa contar mais de um valor de uma única coluna ou de um intervalo de células? Isso significa contar com várias condições e lógica OR. Nesse caso, você pode aplicar as funções SUM e COUNTIFS juntas ou usar a função SUMPRODUCT.
- Contar células com condições OR no Excel
- Contar células com vários conjuntos de condições OR no Excel
Contar células com condições OR no Excel
Por exemplo, tenho uma gama de dados conforme a imagem abaixo mostrada, agora, quero contar o número do produto que é “Lápis” ou “Régua”, aqui, irei discutir duas fórmulas para resolver esta tarefa no Excel.
Contar células com condições OR usando as funções SUM e COUNTIFS
No Excel, para contar com várias condições OR, você pode usar as funções SUM e COUNTIFS com uma constante de matriz, a sintaxe genérica é:
- range: O intervalo de dados contém os critérios de onde você conta as células;
- criterion1, criterion2, criterion3…: As condições nas quais você deseja contar as células.
Para contar o número de produtos que são “Lápis” ou “Régua”, copie ou insira a fórmula abaixo em uma célula em branco e, em seguida, pressione Entrar chave para obter o resultado:
Explicação da fórmula:
= SOMA (CONTADORES (B2: B13, {"Lápis", "Régua"}))
- {"Lápis", "Régua"}: Primeiro, você deve empacotar todas as condições em uma constante de matriz como esta: {"Lápis", "Régua"}, separe os itens por vírgulas.
- COUNTIFS (B2: B13, {"Lápis", "Régua"}): Esta função COUNTIFS obterá contagens individuais para “Lápis” e “Régua”, e você obterá o resultado como este: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Pencil", "Ruler"})) = SUM ({2,3}): Finalmente, esta função SUM soma todos os itens na matriz e retorna o resultado: 5.
Tips: Você também pode usar referências de células para critérios, aplique a fórmula de matriz abaixo e pressione Ctrl + Shift + Enter chaves juntas para obter o resultado correto:
Contar células com condições OR usando a função SUMPRODUCT
Aqui está outra fórmula criada pela função SUMPRODUCT que também pode ajudar a contar células com a lógica OR. A sintaxe genérica é:
- range: O intervalo de dados contém os critérios de onde você conta as células;
- criterion1, criterion2, criterion3…: As condições nas quais você deseja contar as células.
Copie ou insira a seguinte fórmula em uma célula em branco e pressione Entrar chave para retornar o resultado:
Explicação da fórmula:
= SUMPRODUCT (1 * (B2: B13 = {"Lápis", "Régua"}))
- B2: B13 = {"Lápis", "Régua"}: Esta expressão compara cada critério “Lápis”, “Régua” com a célula de intervalo B2: B13. Se o critério for atendido, ele retorna um VERDADEIRO, caso contrário, um FALSO será exibido, você obterá o resultado como este: {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, então obterá o 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; XNUMX}.
- 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 SUMPRODUCT soma todos os números na matriz para obter o resultado: 5.
Contar células com vários conjuntos de condições OR no Excel
Às vezes, você pode precisar contar as células com dois ou mais conjuntos de condições OR; neste caso, você pode usar SUM e COUNTIFS com uma constante de matriz ou SUMPRODUCT com funções ISNUMBER MATCH.
Contar células com dois conjuntos de condições OR usando as funções SUM e COUNTIFS
Para lidar com apenas dois conjuntos de critérios OR, você só precisa adicionar outra constante de matriz à fórmula COUNTIFS.
Por exemplo, eu tenho o intervalo de dados conforme a captura de tela abaixo mostrada, agora, eu quero contar as pessoas que pediram “Lápis” ou “Régua” e o valor é <100 ou> 200.
Insira ou copie a seguinte fórmula em uma célula e pressione Entrar chave para obter o resultado, consulte a imagem:
Nota: Na fórmula, você deve usar um ponto e vírgula para a segunda constante de matriz, que cria uma matriz vertical.
Contar células com vários conjuntos de condições OR usando a função SUMPRODUCT
A fórmula acima funciona apenas para dois conjuntos de critérios OR; se você precisar contar com mais critérios, uma fórmula complexa SUMPRODUCT junto com as funções ISNUMBER MATCH podem ajudá-lo.
Pegue os dados abaixo por exemplo, para contar as pessoas que pediram "Lápis" ou "Régua" e o Status é "Entregue" ou "Em trânsito" e assinado por "Bob" ou "Eko", você deve aplicar uma fórmula complexa .
Copie ou insira a fórmula abaixo em uma célula em branco e pressione Entrar chave para obter o cálculo, consulte a captura de tela:
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 (CORRESPONDÊNCIA (B2: B13, {"Lápis", "Régua"}, 0)):
- CORRESPONDÊNCIA (B2: B13, {"Lápis", "Régua"}, 0): Esta função MATCH é usada para comparar cada célula no intervalo B2: B13 com a constante de matriz correspondente. Se a correspondência for encontrada, ele retorna uma posição relativa do valor na matriz, caso contrário, um valor de erro é exibido. Portanto, você obterá a lista de matrizes como esta: {1; # N / A; # N / A; 2; # N / A; 1; # N / A; 2; 1; # N / A; 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 ISNUMBER converte os números em TRUEs e os valores de erro em FALSEs da seguinte forma: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
A lógica acima também pode ser aplicada à segunda e terceira expressões ISNUMBER.
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, essas três matrizes são multiplicadas juntas dentro de SUMPRODUCT, que converte automaticamente os valores TRUE e FALSE em 1s e 0s como parte da operação matemática como esta: SUMPRODUCT ({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; 1}) = SUCESSO ({0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; XNUMX; XNUMX}).
- Por fim, a função SUMPRODUCT soma todos os números da matriz para obter o resultado: 3.
Função relativa usada:
- SOMA:
- A função SUM do Excel retorna a soma dos valores fornecidos.
- CONT.SE:
- A função CONT.SE é uma função estatística do Excel usada para contar o número de células que atendem a um critério.
- SUMPRODUTO:
- A função SUMPRODUCT pode ser usada para multiplicar duas ou mais colunas ou matrizes e obter a soma dos produtos.
- NÚMERO:
- A função ISNUMBER do Excel retorna TRUE quando uma célula contém um número e FALSE se não.
- PARTIDA:
- A função MATCH do Microsoft Excel procura um valor específico em um intervalo de células e retorna a posição relativa desse valor.
Mais artigos:
- Contar valores numéricos exclusivos com base em critérios
- Na planilha do Excel, você pode ter um problema de contagem do número de valores numéricos exclusivos com base em uma condição específica. Por exemplo, como posso contar os valores de Qty exclusivos do produto “T-shirt” do relatório conforme a captura de tela abaixo mostrada? Neste artigo, mostrarei algumas fórmulas para realizar essa tarefa no Excel.
- Contar células que correspondem a dois ou mais critérios
- No Excel, para contar o número de células com base em dois ou múltiplos critérios, a função COUNTIFS pode ajudá-lo a resolver esta tarefa de forma rápida e fácil.
- Contar o número de linhas com vários critérios OR
- Para contar o número de linhas com vários critérios em colunas diferentes, com a lógica OR, a função SUMPRODUCT pode ajudá-lo. Por exemplo, eu tenho um relatório de produto como a imagem abaixo mostrada, agora, eu quero contar as linhas onde o produto é “Camiseta” ou a cor é “Preta”. Como lidar com essa tarefa no Excel?
As melhores ferramentas de produtividade para escritório
Kutools for Excel - ajuda você a se destacar da multidão
Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.
- Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
- Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
- Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
- Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
