Neste artigo, aprenderemos como contar linhas se isso atender a vários critérios no Excel.
Cenário:
Em palavras simples, ao trabalhar com tabelas de dados, às vezes precisamos contar as células onde mais de dois intervalos atendem aos critérios. Isso pode ser feito usando a fórmula explicada abaixo.
Como resolver o problema?
Para este problema, seremos obrigados a usar a função SUMPRODUCT. Aqui, temos dois intervalos e precisamos da contagem de linhas que atenda a 3 critérios. A função SUMPRODUCT retorna a SOMA dos valores TRUE correspondentes (como 1) e ignora os valores correspondentes aos valores FALSE (como 0) nos retorna uma única matriz onde as condições eram TRUE.
Fórmula genérica:
| = SUMPRODUCT ((rng_1 op_1 crit_1) + 0, (rng_2 op_2 crit_1) + 0, rng_2 op_2 crit_1) + 0) |
rng: intervalo a procurar
crit: critérios a serem aplicados
op: operador de critérios, condição fornecida como operador entre intervalo e critérios
+0: converte valores booleanos em binários (0 e 1).
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui, precisamos encontrar a contagem de linhas listadas no intervalo com 3 condições. Aqui temos uma lista das reuniões diplomáticas realizadas entre a Índia e os EUA a partir de 2014. A tabela mostra o Presidente / PM com a etiqueta do país e ano. A tabela também está dividida em partes que representam o país de origem e a lista dos países visitantes.
Condições listadas abaixo:
O presidente dos EUA "Barack Obama visitou a Índia com menos de 2 problemas.
Use a fórmula:
| = SUMPRODUTO ((C4: C10 = "Barack Obama") + 0, (F4: F10 = "Índia") + 0, (G4: G10 <2) + 0)) |
C4: C10 = "Barack Obama": Presidente que corresponde a "Barack Obama" na lista de visitantes.
F4: F10 = "Índia": país anfitrião que corresponde a "Índia".
G4: G10 <2: questões menores que dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, certa vez o presidente dos EUA, Barack Obama, visitou a Índia, o que aconteceu em 2015. Isso mostra que a fórmula extrai a contagem de vezes correspondida na matriz correspondente. Como houve 1 vez em que o presidente dos EUA "Barack Obama" visitou a Índia, onde as questões também são iguais a 1, que é inferior a 2
Com igual aos critérios:
O exemplo acima foi fácil. Então, para torná-lo interessante, contaremos quantas vezes os EUA hospedaram a Índia a partir de 2014 como dados.
Condições listadas abaixo:
Os EUA hospedaram a Índia com problemas é igual a 2.
Use a fórmula:
| = SUMPRODUTO ((F4: F10 = "EUA") + 0, (D4: D10 = "Índia") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "US": país anfitrião que corresponde a "US".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, houve 2 ocasiões em que os EUA sediaram a Índia e os problemas foram iguais a dois. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve 5 vezes em que os EUA sediaram a Índia, mas os problemas foram 1 ou 3, mas aqui precisamos que os problemas sejam correspondidos com 2.
Com critérios maiores que:
Aqui, para torná-lo interessante, contaremos quantas vezes o presidente dos EUA "Donald Trump" hospedou o primeiro-ministro indiano a partir de 2014 como dados.
Condições listadas abaixo:
O presidente dos EUA, "Donald Trump", hospedou a Índia com problemas maiores que 1.
Use a fórmula:
| = SUMPRODUTO ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "Índia") + 0, (G4: G10> 1) + 0) |
F4: F10 = "EUA": presidente anfitrião correspondendo a "Donald Trump".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, uma vez em que o presidente dos EUA "Donald Trump" hospedou a Índia e questões maiores do que dois. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve 2 vezes em que o presidente dos EUA "Donald Trump" hospedou a Índia, mas os problemas foram 1 ou 3, mas aqui precisamos que os problemas sejam maiores do que 1, que é 3 está no ano de 2019.
Com questões não consideradas nos critérios:
Aqui, para tornar a compreensão mais fácil e conveniente, contaremos quantas vezes no total o presidente dos EUA visitou a Índia a partir de 2014 como dados.
Condições listadas abaixo:
O presidente dos EUA visitou a Índia no total desde 2014.
Use a fórmula:
| = SUMPRODUCT ((F4: F10 = "Índia") + 0, (D4: D10 = "EUA") + 0) |
F4: F10 = "US": país anfitrião que corresponde a "US".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, 2 vezes em que os EUA visitaram a Índia e em edições superiores a duas. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve uma vez em que o presidente dos EUA "Barack Obama" visitou a Índia em 2015 e uma vez quando o presidente dos EUA "Donald Trump" visitou a Índia no ano de 2020.
Você também pode executar intervalos como critérios. Conte as células em que 2 intervalos atendem aos critérios. Saiba mais sobre Countif com SUMPRODUCT no Excel aqui.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números.
- As matrizes na fórmula devem ter comprimento igual, pois a fórmula retorna erro quando não.
- A função SUMPRODUCT considera os valores não numéricos como 0s.
- A função SUMPRODUCT considera o valor lógico TRUE como 1 e False como 0.
- A matriz do argumento deve ter o mesmo tamanho, caso contrário, a função retornará um erro.
- A função SUMPRODUCT retorna a soma depois de obter produtos individuais na matriz correspondente.
- Operadores como é igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou diferente de () pode ser executado dentro de uma fórmula aplicada, apenas com números.
Espero que este artigo sobre como contar linhas que atendem a vários critérios no Excel seja explicativo. Encontre mais artigos sobre fórmulas de contagem aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Encontre a última linha de dados com números no Excel : Em uma faixa de valores de texto, encontre o último dado no Excel.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
COUNTIFS com intervalo de critérios dinâmicos : Contar células dependentes de outros valores de células no Excel.
COUNTIFS Correspondência de dois critérios : Conte as células que correspondem a dois critérios diferentes na lista do Excel.
COUNTIFS com OU para vários critérios : Conte as células com correspondência de vários critérios usando a função OU.
A função COUNTIFS no Excel : Contar células dependentes de outros valores de células.
Como usar Countif em VBA no Microsoft Excel : Conte células usando o código do Visual Basic for Applications.
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.