Como usar várias condições no Excel COUNTIF e COUNTIFS

O Excel possui muitas funções onde o usuário precisa especificar uma ou mais condições para obter o resultado.Por exemplo, se você quiser contar células com base em vários critérios, poderá usar as funções CONT.SE ou CONT.SE no Excel.

Este tutorial abrange várias maneiras de usar um ou vários critérios nas funções CONT.SE e CONT.SE do Excel.

Embora eu me concentre principalmente nas funções COUNTIF e COUNTIFS neste tutorial, todos esses exemplos também podem ser usados ​​com outras funções do Excel que usam vários critérios como entrada (como SOMA, SOMAS, AVERAGEIF e AVERAGEIFS).

Introdução às funções CONT.SE e CONT.SE do Excel

Vamos primeiro entender como usar as funções CONT.SE e CONT.SE no Excel.

Função CONT.SE do Excel (com um único critério)

A função CONT.SE do Excel funciona melhor quando você deseja contar células com base em um único critério.Se você quiser contar com base em vários critérios, use a função COUNTIFS.

sintaxe

=CONT.SE(intervalo, condição)

Parâmetros de entrada

• escopo -  O intervalo de células que você deseja calcular.
• doença -Critérios que devem ser avaliados em relação ao intervalo de células a serem contadas.

Função COUNTIFS do Excel (com vários critérios)

A função COUNTIFS do Excel funciona melhor quando você deseja contar células com base em vários critérios.

sintaxe

=COUNTIFS(intervalo_critério1,intervalo1,[intervalo_critério2,critério2]…)

Parâmetros de entrada

• intervalo_de_critérios1 – O intervalo de células que você deseja avaliar em relação ao critério 1.
• criterio1 – O critério que você deseja avaliar é intervalo_critério1 para determinar quais células avaliar.
• [intervalo_critérios2] – O intervalo de células que você deseja avaliar em relação ao critério 2.
• [critérios2] – O critério que você deseja avaliar é intervalo_critério2 para determinar quais células contar.

Agora vamos ver alguns exemplos de uso de vários critérios na função CONT.SE do Excel.

Use a condição NUMBER na função COUNTIF do Excel

#1 Contar células quando o critério é igual a um valor

Para obter a contagem de células em que o parâmetro de condição é igual ao valor especificado, você pode inserir a condição diretamente ou usar uma referência de célula que contenha a condição.

Abaixo está um exemplo onde contamos células que contêm o número 9 (o que significa que o parâmetro de condição é igual a 9).Aqui está a fórmula:

=CONT.SE($B$2:$B$11,D3)

No exemplo acima (na imagem), o critério está na célula D3.Você também pode inserir condições diretamente nas fórmulas.Por exemplo, você também pode usar:

=CONT.SE($B$2:$B$11,9)

#2 Contar células quando a condição for maior que o valor

Para obter a contagem de células com um valor maior que um valor especificado, usamos o operador maior que (">").Podemos usá-lo diretamente em fórmulas ou podemos usar referências de células com condições.

Sempre que usamos um operador em uma condição no Excel, precisamos colocá-lo entre aspas duplas.Por exemplo, se a condição for maior que 10, precisamos inserir ">10" como condição (veja a imagem abaixo):

Aqui está a fórmula:

=CONT.SE($B$2:$B$11,”>10″)

Você também pode incluir condições em células e usar referências de células como condições.Nesse caso, você não precisa colocar a condição entre aspas duplas:

=CONT.SE($B$2:$B$11,D3)

Isso também pode ser o caso quando você deseja que uma condição esteja em uma célula, mas não deseja usá-la com um operador.Por exemplo, você pode querer que o número na célula D3 seja 10 em vez de >10.

Nesse caso, você precisa criar um parâmetro condicional, que é uma combinação de operador e referência de célula (veja a imagem abaixo):

=CONT.SE($B$2:$B$11,">"&D3)

Nota: Quando você combina operadores e referências de célula, os operadores são sempre colocados entre aspas duplas.Operadores e referências de célula são conectados por um e comercial (&).

#3 Contar células quando o critério for menor que um valor

Para obter a contagem de células com um valor menor que um valor especificado, usamos o operador menor que ("<").Podemos usá-lo diretamente em fórmulas ou podemos usar referências de células com condições.

Sempre que usamos um operador em uma condição no Excel, precisamos colocá-lo entre aspas duplas.Por exemplo, se o critério é que a quantidade seja menor que 5, então precisamos inserir "<5" como critério (veja a imagem abaixo):

=CONT.SE($B$2:$B$11,"<5")

Você também pode incluir condições em células e usar referências de células como condições.Neste caso você não precisa colocar o padrão entre aspas duplas (veja a imagem abaixo):

=CONT.SE($B$2:$B$11,D3)

Além disso, você pode querer que a condição esteja na célula, mas não com o operador.Por exemplo, você pode querer que a célula D3 tenha o número 5 em vez de <5.

Nesse caso, você precisa criar um parâmetro condicional que é uma combinação de operador e referência de célula:

=CONT.SE($B$2:$B$11,"<"&D3)

Nota: Quando você combina operadores e referências de célula, os operadores são sempre colocados entre aspas duplas.Operadores e referências de célula são conectados por um e comercial (&).

#4 Contar células com vários critérios - entre dois valores

Para obter a contagem de valores entre dois valores, precisamos usar várias condições na função CONT.SE.

Aqui estão duas maneiras de fazer isso:

Método XNUMX: use a função CONT.SE

A função COUNTIFS pode manipular várias condições como argumentos e contar células somente se todas as condições forem TRUE.Para contar células com valores entre dois valores especificados, como 5 e 10, podemos usar a seguinte função COUNTIFS:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Nota: A fórmula acima não conta células que contenham 5 ou 10.Se você quiser incluir essas células, use os operadores maior ou igual a (>=) e menor ou igual a (<=).Aqui está a fórmula:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Você também pode incluir essas condições nas células e usar referências de células como condições.Neste caso você não precisa colocar o padrão entre aspas duplas (veja a imagem abaixo):

Você também pode usar uma combinação de referências de células e operadores (inserindo operadores diretamente nas fórmulas).Ao combinar operadores e referências de célula, os operadores são sempre colocados entre aspas duplas.Operadores e referências de célula são conectados por um e comercial (&).

Método 2: use duas funções CONT.SE

Se você tiver várias condições, poderá usar COUNTIFS ou criar uma combinação de funções COUNTIF.A fórmula a seguir fará a mesma coisa:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

Na fórmula acima, primeiro encontramos o número de células com valor maior que 5 e depois subtraímos o número de células com valor maior que 10.Isso nos dará um resultado de 5 (ou seja, com valores maiores que 5 e menores ou iguais a 10).

Se você quiser que a fórmula contenha 5 e 10, use a seguinte fórmula:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Se você quiser que a fórmula exclua "5" e "10" da contagem, use a seguinte fórmula:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Você pode incluir essas condições nas células e usar referências de células ou pode usar uma combinação de operadores e referências de células.

Usar condições de TEXTO em funções do Excel

#1 Contar células quando a condição for igual ao texto especificado

Para contar células que contenham uma correspondência exata com um texto especificado, podemos simplesmente usar esse texto como condição.Por exemplo, em um conjunto de dados (mostrado abaixo), se eu quisesse contar todas as células chamadas Joe, eu poderia usar a seguinte fórmula:

=CONT.SE($B$2:$B$11,"Joe")

Como esta é uma string de texto, preciso colocar a condição de texto entre aspas duplas.

Você também pode incluir uma condição em uma célula e, em seguida, usar essa referência de célula (como mostrado abaixo):

=CONT.SE($B$2:$B$11,E3)

Nota: Você pode obter resultados errados se houver espaços à esquerda/à direita na condição ou intervalo de condição.Certifique-se de limpar os dados antes de usar essas fórmulas.

#2 Contar células quando a condição não for igual ao texto especificado

Semelhante ao que vimos no exemplo acima, você também pode contar células que não contenham texto especificado.Para fazer isso, precisamos usar o operador não é igual (<>).

Supondo que você queira contar todas as células que não contêm o nome JOE, aqui está uma fórmula que pode fazer isso:

=CONT.SE($B$2:$B$11,"<>Joe")

Você também pode incluir condições em células e usar referências de células como condições.Neste caso você não precisa colocar o padrão entre aspas duplas (veja a imagem abaixo):

=CONT.SE($B$2:$B$11,E3)

Isso também pode ser o caso quando você deseja que uma condição esteja em uma célula, mas não deseja usá-la com um operador.Por exemplo, você pode querer que a célula D3 tenha o nome Joe em vez de <>Joe.

Nesse caso, você precisa criar um parâmetro condicional, que é uma combinação de operador e referência de célula (veja a imagem abaixo):

=CONT.SE($B$2:$B$11,"<>"&E3)

Ao combinar operadores e referências de célula, os operadores são sempre colocados entre aspas duplas.Operadores e referências de célula são conectados por um e comercial (&).

Usando condições de DATA nas funções COUNTIF e COUNTIFS do Excel

O Excel armazena datas e horas como números.Então, podemos usá-lo como um número.

#1 Contar células quando a condição for igual à data especificada

Para obter a contagem de células que contêm uma data especificada, usaremos o operador equals (=) junto com a data.

Para trabalhar com datas, recomendo usar a função DATE, pois elimina qualquer chance de o valor da data estar errado.Então, por exemplo, se eu quisesse usar a data 2015º de setembro de 9, eu poderia usar a função DATE assim:

= DATA (2015,9,1)

Apesar das diferenças regionais, esta fórmula retornará na mesma data.Por exemplo, 01-09-2015 seria 2015º de setembro de 9 na sintaxe de data dos EUA e 1 de janeiro de 2015 na sintaxe de data do Reino Unido.No entanto, essa fórmula sempre retornará 2105º de setembro de 9.

Aqui está a fórmula para contar o número de células contendo a data 02-09-2015:

=CONT.SE($A$2:$A$11,DATA(2015,9,2))

#2 Contar células quando a condição é anterior ou posterior à data especificada

Para contar células que contêm datas anteriores ou posteriores a uma data especificada, podemos usar operadores menor que/maior que.

Por exemplo, se eu quisesse contar todas as células contendo datas após 2015 de setembro de 9, eu poderia usar a seguinte fórmula:

=CONT.SE($A$2:$A$11,">"&DATA(2015,9,2))

Da mesma forma, você também pode contar o número de células antes de uma data especificada.Se você quiser incluir datas na contagem, use o operador "igual" junto com os operadores "maior que/menor que".

Você também pode usar referências de células que contenham datas.Nesse caso, você precisa usar um e comercial (&) para combinar o operador (entre aspas duplas) com a data.

Veja o exemplo abaixo:

=CONT.SE($A$2:$A$11,">"&F3)

#3 Contar células com vários critérios - entre duas datas

Para obter a contagem de valores entre dois valores, precisamos usar várias condições na função CONT.SE.

Podemos fazer isso de duas maneiras - uma única função COUNTIFS ou duas funções COUNTIF.

Método XNUMX: use a função CONT.SE

A função COUNTIFS pode receber várias condições como argumentos e contar células somente se todas as condições forem TRUE.Para contar células com valores entre duas datas especificadas, como 9 e 2 de setembro, podemos usar a seguinte função COUNTIFS:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

A fórmula acima não conta as células que contêm a data especificada.Se você também quiser incluir essas datas, use os operadores maior ou igual a (>=) e menor ou igual a (<=).Aqui está a fórmula:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Você também pode incluir datas nas células e usar referências de células como critérios.Nesse caso, você não pode fazer com que o operador inclua a data na célula.Você precisa adicionar operadores manualmente à fórmula (entre aspas duplas) e usar um e comercial (&) para adicionar referências de célula.Ver abaixo:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Método 2: use a função CONT.SE

Se você tiver várias condições, poderá usar uma função CONT.SE ou criar uma combinação de duas funções CONT.SE.A fórmula a seguir também fará o truque:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Na fórmula acima, primeiro encontramos o número de células cuja data é posterior a 9 de setembro e, em seguida, subtraímos o número de células cuja data é posterior a 2 de setembro.Isso dará um resultado de 9 (ou seja, o número de células com datas após 7 de setembro e em ou antes de 7 de setembro).

Se você não quiser que a fórmula calcule 9 e 2 de setembro, use a seguinte fórmula:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Se você deseja excluir duas datas da contagem, use a seguinte fórmula:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Além disso, você pode definir datas padrão nas células e usar referências de células (e operadores entre aspas duplas com e comercial).

Usando curingas nas condições das funções COUNTIF e COUNTIFS

Existem três curingas no Excel:

1. *(Asterisco) - representa qualquer número de caracteres.Por exemplo, ex* pode significar excel, excels, example, expert, etc.
2. ? (ponto de interrogação) - Representa um único caractere.Por exemplo, Trömp pode significar Trump ou Vagabundo.
3. ~ (til) - Usado para identificar caracteres curinga (~, *, ?) no texto.

Você pode usar a função CONT.SE com curingas para contar células quando outras funções de contagem internas falharem.Por exemplo, suponha que você tenha um conjunto de dados parecido com este:

Agora vamos a alguns exemplos:

#1 Contar células que contêm texto

Para contar células com texto, podemos usar o curinga * (asterisco).Como os asteriscos representam qualquer número de caracteres, ele contará todas as células que contenham qualquer texto.Aqui está a fórmula:

=CONT.SE($C$2:$C$11,"*")

Observação: a fórmula acima ignora células que contêm números, células em branco e valores lógicos, mas conta as células que contêm apóstrofos (e, portanto, aparecem em branco) ou que podem ter sido incluídas como parte da fórmula.

Aqui está um tutorial detalhado sobre como lidar com casos com strings ou apóstrofos vazios.

#2 Contar células não vazias

Se você está pensando em usar a função COUNTA, pense novamente.

Experimente, pode decepcioná-lo.COUNTA também contará células que contêm strings vazias (geralmente retornadas por fórmulas como ="", ou quando as pessoas apenas inserem apóstrofos nas células).As células que contêm strings vazias parecem estar em branco, mas não são, portanto, são contadas pela função COUNTA.

COUNTA também contará células que contêm strings vazias (geralmente retornadas por fórmulas como ="", ou quando as pessoas apenas inserem apóstrofos nas células).As células que contêm strings vazias parecem estar em branco, mas não são, portanto, são contadas pela função COUNTA.

Então, se você usar a fórmula = COUNTA (A1:A11), ele retorna 11 quando deveria retornar 10.

Aqui está a correção:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Vamos entender essa fórmula dividindo-a:

#3 Contar células contendo texto específico

Suponha que queremos contar todas as células em que o nome do representante de vendas começa com J.Isso pode ser feito facilmente usando curingas na função CONT.SE.Aqui está a fórmula:

=CONT.SE($C$2:$C$11,"J*") 

A condição J* especifica que o texto na célula deve começar com J e pode conter qualquer número de caracteres.

Se você quiser contar células que contenham letras em qualquer parte do texto, coloque-as entre um asterisco.Por exemplo, se você quiser contar células que contenham a letra "a", use *a* como condição.

Comparado aos meus outros artigos, este é extraordinariamente longo.Espero que você goste.Deixe-me saber o que você pensa deixando um comentário.