Função COUNTIFS, como usar a função de contagem de células de acordo com múltiplas condições no Excel

A função COUNTIFS no Excel tem muitas aplicações práticas. Aqui está o que você precisa saber sobre como usar a função COUNTIFS no Excel .

O Microsoft Excel é um software de planilha famoso no mundo com muitos recursos úteis. O advento do software torna a entrada de dados e o cálculo de uma série de números mais simples do que nunca. E ao usar o Excel, você definitivamente precisa conhecer a função COUNTIFS .

A função COUNTIFS no Excel é usada para contar células que satisfazem muitas condições específicas. A função CONT.SE é uma das funções estatísticas do Excel mais utilizadas no Excel. É uma função avançada da função CONT.SE que conta apenas células com uma determinada condição. Ao trabalhar com a função COUNTIFS, os usuários podem encontrar facilmente as células de resultado que atendem às condições fornecidas na solicitação. As condições podem ser números, datas, texto ou células contendo dados. O artigo abaixo irá guiá-lo como usar a função COUNTIFS no Excel.

• Como comparar dados em 2 colunas do Excel
• Como usar a função DSUM para calcular somas com condições complexas no Excel
• Como usar a função SUMIF para calcular somas condicionais no Excel
• Uso da função VLOOKUP e exemplos específicos

Instruções para usar a função COUNTIFS no Excel

A sintaxe da função COUNTIFS é =COUNTIFS(intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2],…) .

Lá:

• Criteria_range1 é o primeiro intervalo de seleção que precisa de estatísticas, um valor obrigatório.
• Critério1 é uma condição aplicada ao intervalo_critérios1 de seleção, o valor necessário pode ser uma célula, expressão ou texto.
• [intervalo_critérios2, critérios2] são pares adicionais de seleção e condição, permitindo até 127 pares de seleção e condição.

Observação ao usar a função COUNTIFS:

• As seleções adicionais precisam ter o mesmo número de linhas e colunas que a área intervalo_critérios1 e podem ser separadas umas das outras.
• Se a condição de seleção se referir a uma célula vazia, a função COUNTIFS considera automaticamente o valor como 0.
• Os caracteres ? podem ser usados. Para substituir um determinado caractere, o sinal * substitui uma sequência inteira de caracteres. Se você precisar encontrar uma placa? ou um sinal * real, digite ~ na frente desse caractere.
• Observe que a função COUNTIFS não diferencia maiúsculas de minúsculas.
• Em geral, os valores de texto precisam ser colocados entre aspas, mas os números não. No entanto, quando um operador lógico é incluído com um número, o número e o operador devem ser colocados entre aspas, conforme mostrado abaixo:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Nota : Condições adicionais devem seguir as mesmas regras.

• Ao usar um valor de outra célula em uma condição, a referência da célula deve ser concatenada com um operador quando usada. No exemplo abaixo, COUNTIFS contará valores em A1:A10 que sejam menores que o valor na célula B1. Observe que o operador menor que (que é texto) está entre aspas, mas a referência da célula não é necessária:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Nota : COUNTIFS é uma das várias funções que dividem as condições em duas partes: Faixa + critérios. Isso causa algumas inconsistências com outras fórmulas e funções.

• COUNTIFS pode contar células em branco ou não. As fórmulas abaixo contam células em branco e não em branco no intervalo A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• A maneira mais fácil de usar COUNTIFS com datas é referir-se a uma data válida em outra célula que tenha uma referência de célula. Por exemplo, para contar células em A1:A10 que contenham uma data maior que uma data em B1, você pode usar uma fórmula como esta:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Exemplo de como usar a função COUNTIFS no Excel

1. Folha de dados nº 1

Temos a tabela de dados abaixo para fazer algumas solicitações à tabela.

Exemplo 1: Calcule o número de funcionários do sexo masculino com 25 dias úteis.

Na célula onde precisamos inserir o resultado, insira a fórmula =COUNTIFS(C2:C7,"Male",D2:D7,25) e pressione Enter.

Lá:

• C2:C7 é uma área obrigatória de 1 contagem com o Gênero do funcionário.
• Sul é a condição da área de contagem 1.
• C2:C7 é a 2ª área de contagem com a Jornada de Trabalho do funcionário.
• 25 é a condição da região de contagem 2.

Como resultado, temos 2 funcionários, Nam, com 25 dias úteis.

Exemplo 2: Calcule o número de funcionários do sexo masculino cujo dia de folga é 0.

Na caixa de entrada da fórmula, insira =COUNTIFS(C2:C7,"Nam",E2:E7,0) e pressione Enter.

Os resultados mostram 1 funcionário do sexo masculino com 0 dias de folga.

Exemplo 3: Calcule a quantidade de funcionários do sexo masculino que têm 25 dias de trabalho e folgas menores ou iguais a 2 dias.

Insira a fórmula =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> e pressione Enter.

Como resultado, existem 2 colaboradores do sexo masculino que cumprem os requisitos de folgas inferiores ou iguais a 2 dias.

2. Folha de dados número 2

Exemplo 1: Calcule o total de produtos taiwaneses com preço unitário inferior a 200.000 VND.

Na caixa de entrada de resultados, inserimos a fórmula de cálculo =COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<> e pressione Enter.

O resultado foram 2 produtos taiwaneses que atenderam aos requisitos.

Exemplo 2: Calcule o total de produtos taiwaneses com preços unitários entre 100.000 VND e 150.000 VND.

Na caixa de entrada de resultados, insira a fórmula =COUNTIFS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<> e pressione Enter.

Como resultado, haverá 2 produtos taiwaneses com preços entre 100.000 VND e 150.000 VND.

Como usar a função COUNTIFS combinada com SUM no Excel

A função CONT.SE é essencialmente uma versão mais complexa da função CONT.SE. A principal vantagem do COUNTIFS sobre o COUNTIF é que ele suporta múltiplas condições e intervalos.

No entanto, você também pode definir um intervalo e uma única condição para a função CONT.SE, assim como fez com a função CONT.SE.

Uma coisa importante que você deve entender sobre a função COUNTIFS antes de usá-la é que ela faz mais do que simplesmente somar os resultados das células que atendem aos critérios de cada intervalo de células.

Na verdade, se você tiver duas condições para dois intervalos, as células do primeiro intervalo serão filtradas duas vezes: uma vez pela primeira condição e depois pela segunda condição. Isso significa que a função COUTNIFS retornará apenas valores que atendam a ambas as condições, dentro do intervalo determinado.

Você pode entender melhor a funcionalidade da função COUNTIFS estudando o exemplo abaixo.

Exemplo da função COUNITFS no Excel

Além da cor das maçãs, existe também uma coluna que descreve o seu tamanho. O objetivo final neste exemplo é contar o número de maçãs vermelhas grandes.

1. Selecione a célula onde deseja exibir os resultados. (Neste exemplo, o artigo exibirá o grande número de maçãs vermelhas na célula E3).

2. Vá para a barra de fórmulas e insira a fórmula abaixo:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Com este comando, a fórmula verifica as células A3 a A11 quanto à condição “Vermelho”. As células que passam neste teste são então testadas novamente no intervalo B3 a B11 para a condição “Grande” .

4. Pressione o botão Entrar.

5. Agora, o Excel contará o número de maçãs vermelhas grandes.

Resultados de exemplo da função COUNTIFS do Excel

Observe como a fórmula conta as células que possuem os atributos vermelho e grande. A fórmula pega as células A3 a A11 e as testa para encontrar o resultado vermelho. As células que passam nesta condição são então testadas novamente em relação à próxima condição no segundo intervalo, neste caso, a condição grande.

Em conclusão, os intervalos e condições após o primeiro intervalo e condição estreitam cada vez mais o filtro de contagem e não são independentes uns dos outros. Então, o resultado final da receita são maçãs vermelhas e grandes. Você pode contar o número de maçãs vermelhas ou grandes combinando a função CONT.SE com a função SOMA .

1. Selecione a célula onde deseja exibir os resultados da fórmula. (Neste exemplo, o artigo utilizará a célula E6).

2. Insira a fórmula abaixo:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Esta fórmula contará as células que contêm maçãs vermelhas, depois o número de células que contêm maçãs grandes e, por fim, somará esses dois números.

4. Pressione o botão Entrar.

5. O Excel agora contará e exibirá o número de maçãs grandes ou vermelhas.

Combine a função CONT.SE com a função SUM

Acima estão 2 tabelas de dados com uso para a função COUNTIFS no Excel. Os usuários devem escrever as condições que acompanham a área de dados de condição para que a função COUNTIFS determine corretamente.

Como corrigir erros comuns ao usar a função COUNTIFS no Excel

COUNTIFS não funciona ao contar valores de texto

Ao contar cadeias de texto, elas devem ser inseridas entre aspas. Caso contrário, a função COUNTIFS não será capaz de contar a sequência de texto e retornará o valor 0. Na imagem de exemplo a seguir, a sequência de texto não foi colocada entre aspas. Portanto, esta fórmula retorna 0.

Para corrigir esse erro, você só precisa reescrever exatamente a fórmula: =COUNTIFS(E5:E12, "Car")

COUNTIFS não funciona porque a referência do intervalo está errada

Ao usar mais de um critério na função COUNTIFS, o intervalo de células para os outros critérios deve ter o mesmo número de células. Caso contrário, a função COUNTIFS não funcionará.

Digamos que neste exemplo queremos contar as vendas de carros em Austin. A fórmula inserida é =COUNTIFS(E5:E12,"Carro",D5:D11,"Austin"). Olhando atentamente para a fórmula, você verá que o primeiro intervalo de critérios aqui é E5:E12, mas o segundo intervalo de critérios é D5:D11. O número de células no intervalo para este critério não é o mesmo.

Agora pressione Enter , a fórmula retornará #VALOR! .

Reescreva a fórmula corretamente da seguinte forma: =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")

COUNTIFS não funciona devido a um erro na fórmula

Se a fórmula não for inserida corretamente, a função COUNTIFS não funcionará. Ao usar qualquer operador matemático, como maior que (>), menor que (<), igual a="" (=")" e "not="" equals=""> <> ) , tanto o operador quanto o o critério numérico deve ser inserido dentro da mesma equação. Por exemplo, se quiser encontrar vendas superiores a US$ 100.000, insira a seguinte fórmula:

=COUNTIFS(F5:F12,">" 100000)

Aqui apenas o operador é inserido dentro da equação, não há critério numérico.

Se você pressionar Enter, a caixa de diálogo de mensagem do Microsoft Excel exibirá: “Há um problema com esta fórmula”.

Para consertar o problema. Digite a fórmula exata:

=COUNTIFS(F5:F12,">100000")

Agora inserimos o operador e os critérios entre parênteses. Agora esta fórmula retornará a quantidade necessária.

Pressione Enter.

Como resultado, você receberá um valor de vendas superior a 100.000USD.

Desejo-lhe sucesso!