A função CONT.SEIS do Excel é usada para obter o número de células em um ou mais intervalos que atendem a um conjunto de condições ou critérios especificados.
Ocasionalmente, a função CONT.SE não funciona como esperado e retorna um #VALOR ! 0 , uma mensagem que indica que há um erro na fórmula ou algum outro valor inesperado.
Aqui estão algumas das razões pelas quais a função não funciona como esperado no Microsoft Excel:
- Quando uma fórmula se refere a um intervalo em uma pasta de trabalho que está fechada.
- Usar um valor de outra célula sem concatená-lo ao operador lógico usado.
- Não colocar critérios de texto entre aspas (” “).
- Os intervalos subsequentes não têm o mesmo número de colunas e linhas que o primeiro intervalo de critérios.
- Os critérios numéricos e o operador lógico não são colocados entre aspas (” “).
- Tentativa de obter um número usando a lógica OR.
- Alterações subsequentes no conjunto de dados.
- Erros na fórmula.
Neste tutorial, explicamos como esses motivos podem ser resolvidos para que a função retorne o número correto que estamos procurando.
1. A fórmula se refere a um intervalo em outra pasta de trabalho que está fechada.
Quando a função CONTRIBUIÇÕES se refere a um intervalo em outra pasta de trabalho fechada, ela retorna um erro #VALOR! em vez do número esperado de células.
No exemplo a seguir, em vez de a fórmula retornar o número de produtos vermelhos, foi gerado um erro #VALUE! porque a fórmula se refere a um intervalo em outra pasta de trabalho fechada.
Solução
A solução para o fato de as contas não funcionarem nesse cenário é abrir a pasta de trabalho fechada e pressionar a tecla F9 no teclado para que a fórmula recalcule os parâmetros corretos. Assim, obteremos a conta correta, conforme mostrado no exemplo a seguir.
2. Usar o valor de outra célula sem concatenar a referência da célula ao operador lógico
Se você usar um operador lógico e uma referência de célula como critérios para a função CONTRIBUIÇÕES em uma fórmula e não concatenar a referência de célula ao operador lógico inserindo & antes da referência de célula, a função retornará um 0 em vez do número esperado.
Isso é ilustrado no exemplo a seguir, em que a fórmula =COUNTIFS(G2:G6,”>M2″) foi usada.
Solução
No exemplo a seguir, precisamos inserir a fórmula correta =COUNTIFS(G2:G6,”> ” &M2) na célula I3. Quando pressionamos ENTER , obtemos o número correto de 4.
Na fórmula correta, somente o operador lógico maior que (>) é colocado entre aspas (” “) e o operador de concatenação & é colocado antes da referência da célula M2.
3. Não coloque aspas (” “) ao redor dos critérios de texto
Se os critérios de texto não forem colocados entre vírgulas, a função CONT.SE retornará um valor 0 em vez do número correto esperado, conforme mostrado no exemplo abaixo.
A fórmula na célula J2 é =COUNTIFS(C2:C6,Red). A fórmula retorna 0 porque o critério Red não foi colocado entre aspas (” “).
Solução para o problema das contas que não funcionam
Precisamos colocar os critérios de texto na fórmula entre aspas, conforme mostrado no exemplo a seguir, e então obteremos o número correto de 2.
4. O próximo conjunto de critérios não tem a mesma forma que o primeiro conjunto de critérios.
Quando usamos vários critérios na função COUNTIFS e os intervalos de critérios opcionais adicionais não correspondem ao primeiro intervalo de critérios, é gerado o erro #VALUE.
No exemplo a seguir, a fórmula =COUNTIFS(B2:B13, “Red”,C2:C10, “Microsoft”) foi inserida na célula G3 em uma tentativa de obter o número de produtos vermelhos fornecidos pela Microsoft.
Quando pressionamos Enter, recebemos o erro #VALUE!, conforme mostrado abaixo.
Isso ocorre porque o segundo intervalo dos critérios C2:C10 não tem o mesmo número de células que o primeiro intervalo dos critérios B2:B13.
Solução
Para corrigir esse problema, precisamos nos certificar de que o segundo intervalo de critérios tenha o mesmo tamanho que o primeiro intervalo de critérios, conforme mostrado abaixo:
Quando pressionamos ENTER, obtemos o número correto de 2, conforme mostrado abaixo:
5. Não coloque critérios numéricos e o operador lógico entre aspas (” “)
Quando usamos operadores lógicos como igual a (=), maior que (>), menor que (<) e diferente de (<>) em uma fórmula e não inserimos o critério numérico e o operador lógico entre as mesmas aspas, o Excel nos envia uma mensagem indicando que a fórmula contém um erro.
No exemplo a seguir, não colocamos o operador lógico e o critério numérico entre aspas. Somente o operador lógico está entre aspas.
Quando pressionamos ENTER, recebemos a seguinte mensagem:
Solução
Precisamos nos certificar de que incluímos o operador lógico e o critério numérico dentro das mesmas aspas, conforme mostrado abaixo:
Quando pressionamos ENTER, obtemos o número correto de 5, conforme mostrado abaixo:
6. Tentativa de obter uma contagem usando a lógica OR
Embora a função COUNTIFS possa gerar contagens corretas usando a lógica AND, ela gera apenas um valor 0 quando tentamos usá-la para calcular usando a lógica OR.
No exemplo a seguir, estamos tentando obter o número de categorias de produtos vermelhos e azuis usando a lógica OR.
Quando pressionamos ENTER, obtemos o valor inesperado de 0, conforme mostrado abaixo. Isso ocorre porque a função COUNTIFS não pode calcular usando a lógica OR.
Solução
Podemos contornar esse problema de lógica OU usando as funções COUNTIFS e SUM juntas.
No exemplo a seguir, inserimos a fórmula =SUM(COUNTIFS(B2:B13,{“Red”, “Blue”})) na célula G3.
Quando pressionamos ENTER, obtemos o valor correto de 6, conforme mostrado abaixo. Isso ocorre porque a função COUNTIFS retornou 3 números de vermelhos e 3 números de azuis do intervalo de critérios B2:B13 e a função SUM adicionou os valores para obter 6.
7. Alterações subsequentes no conjunto de dados
As alterações em um conjunto de dados podem fazer com que a função COUNTIFS não gere os valores esperados. Por exemplo, no conjunto de dados a seguir, a função COUNTIF funciona corretamente e retorna o número correto de produtos azuis.
Mas se houver alterações nos dados, por exemplo, neste caso, adicionamos a palavra cor a Azul, a função COUNTIFS agora exibe o valor incorreto de 0.
Solução
Essa situação pode ser resolvida com o uso de curingas com a função COUNTIF.
No exemplo a seguir, usamos o caractere asterisco (*) nos critérios, o que nos permite obter o número correto de 4 produtos. A função CONT.SE agora pode pesquisar correspondências parciais no intervalo de critérios.
8. Erros na fórmula
Às vezes, a função CONT.SE não funciona devido a um erro na fórmula, como, por exemplo, o esquecimento de uma vírgula de separação entre o intervalo de critérios e o operador lógico.
No exemplo a seguir, não há vírgula entre o intervalo de critérios D2:D13 e o operador lógico maior que (>).
Quando pressionamos ENTER, obtemos uma caixa de mensagem informando que há um erro na fórmula.
Solução
É preciso ter muito cuidado ao escrever fórmulas para garantir que a sintaxe esteja correta. No exemplo a seguir, a inserção de uma vírgula entre o intervalo de critérios e o operador maior que (>) resolve o problema e obtemos o número correto de 5.
Conclusão
COUNTIFS é uma função muito poderosa que pode ser usada para gerar o número de células em intervalos de dados que atendem a condições específicas.
Neste tutorial, examinamos os vários motivos pelos quais essa função nem sempre funciona como esperado e as maneiras de resolvê-los.