Você pode descobrir que sua fórmula COUNTIF retorna zero ou um erro ao tentar contar células com textos longos. Isso acontece porque a função COUNTIF tem uma limitação oculta: ela não consegue avaliar corretamente critérios com mais de 255 caracteres. Este artigo explica esse limite técnico e fornece vários métodos confiáveis para contar células com texto de qualquer comprimento.
Principais conclusões: Contar texto longo no Excel
- SUMPRODUCT com EXACT: Use esta fórmula matricial para fazer uma contagem que diferencia maiúsculas de minúsculas de células que correspondem exatamente a um texto longo.
- SUMPRODUCT com curingas: Conte células que contêm um fragmento de texto longo usando o asterisco (*) como curinga dentro da função SUMPRODUCT.
- Coluna auxiliar com LEN: Adicione uma coluna para sinalizar células com texto acima de 255 caracteres e depois use COUNTIF nos resultados da coluna auxiliar.
Por que o COUNTIF falha com texto acima de 255 caracteres
As funções COUNTIF e COUNTIFS foram projetadas para contagem condicional rápida. Internamente, elas usam um mecanismo de comparação mais antigo que trunca qualquer texto no argumento de critério para 255 caracteres. Se o valor de pesquisa ou o texto em uma célula exceder esse limite, a comparação se torna inválida. A função compara os primeiros 255 caracteres do seu critério com os primeiros 255 caracteres de cada célula. Se o texto for idêntico nos primeiros 255 caracteres, mas diferente no caractere 256, o COUNTIF ainda contará como correspondência, gerando resultados incorretos. Para textos longos únicos, geralmente retorna zero porque a comparação truncada não encontra correspondência.
O limite técnico em funções mais antigas
Essa restrição vem da compatibilidade com formatos de arquivo mais antigos. Funções como COUNTIF, SUMIF, VLOOKUP e MATCH compartilham essa limitação ao usar critérios de texto. Isso não afeta comparações numéricas. As funções mais recentes XLOOKUP e XMATCH não têm essa restrição e podem lidar corretamente com textos longos, mas não há um substituto direto para COUNTIF. Portanto, você precisa usar construções de fórmula alternativas para obter uma contagem precisa.
Métodos para contar células com textos longos
Você pode usar a função SUMPRODUCT para contornar o limite de caracteres. SUMPRODUCT pode processar matrizes e aplicar testes lógicos a cada célula em um intervalo sem a restrição de 255 caracteres. Escolha o método com base na necessidade de correspondência exata ou parcial.
Correspondência exata para um texto longo específico
Para contar células que correspondem exatamente a um texto muito longo, combine SUMPRODUCT com a função EXACT. EXACT diferencia maiúsculas de minúsculas. Suponha que seu texto longo esteja na célula F1 e o intervalo a verificar seja A1:A100.
- Insira a fórmula para correspondência exata
Em uma célula vazia, digite a fórmula:=SUMPRODUCT(--(EXACT(A1:A100, F1))). O hífen duplo (–) converte os resultados VERDADEIRO/FALSO de EXACT em 1 e 0 que SUMPRODUCT pode somar. - Pressione Enter para concluir a contagem
O Excel calculará o resultado, comparando corretamente o texto completo em cada célula de A1:A100 com o texto completo em F1, independentemente do comprimento.
Correspondência parcial para texto contendo um fragmento longo
Se você precisa contar células que contêm uma substring longa específica, use SUMPRODUCT com as funções ISNUMBER e SEARCH. SEARCH localiza texto dentro de uma célula e não diferencia maiúsculas de minúsculas.
- Insira a fórmula para correspondência parcial
Em uma célula vazia, digite:=SUMPRODUCT(--(ISNUMBER(SEARCH(F1, A1:A100)))). Substitua F1 pelo seu texto de pesquisa longo e A1:A100 pelo intervalo alvo. - Pressione Enter para obter a contagem
A fórmula contará todas as células onde o texto longo em F1 for encontrado em qualquer lugar do conteúdo da célula. Para uma correspondência parcial que diferencia maiúsculas de minúsculas, use a função FIND em vez de SEARCH.
Usando uma coluna auxiliar para identificar texto longo
Quando você precisa contar ou filtrar células repetidamente com base no comprimento do texto, adicionar uma coluna auxiliar é eficiente. Este método sinaliza quais células contêm texto que excede o limite de 255 caracteres.
- Insira uma nova coluna ao lado dos seus dados
Para dados na coluna A, clique com o botão direito no cabeçalho da coluna B e selecione Inserir para criar uma nova coluna auxiliar. - Insira a fórmula de verificação de comprimento
Na célula B1, insira a fórmula:=LEN(A1)>255. Esta fórmula retorna VERDADEIRO se o texto em A1 for maior que 255 caracteres. - Copie a fórmula para baixo na coluna
Clique duas vezes no alça de preenchimento (o pequeno quadrado no canto inferior direito da célula B1) para copiar a fórmula até o final do seu conjunto de dados. - Conte as células sinalizadas
Agora você pode usar um COUNTIF padrão na coluna auxiliar:=COUNTIF(B:B, VERDADEIRO). Isso conta todas as células onde o comprimento do texto excede 255 caracteres.
Se sua contagem ainda retornar resultados inesperados
Mesmo com a fórmula correta, outros fatores podem causar contagens incorretas.
Fórmulas retornam erros ao contar texto longo
Se sua fórmula SUMPRODUCT retornar um erro #VALOR!, o problema geralmente é uma incompatibilidade nos tamanhos dos intervalos. Certifique-se de que o intervalo no seu critério (por exemplo, F1) seja uma única célula ou uma matriz que corresponda à altura do seu intervalo de dados. Além disso, verifique se há caracteres não imprimíveis no seu texto longo, como quebras de linha, que podem afetar a comparação. Use a função CLEAN nos seus dados primeiro: =SUMPRODUCT(--(EXACT(CLEAN(A1:A100), CLEAN(F1)))).
Contagem é zero para texto aparentemente correspondente
Se a contagem for zero, verifique se não há espaços iniciais ou finais. Uma única diferença de espaço fará com que EXACT retorne FALSO. Use a função TRIM dentro da sua fórmula para remover espaços extras: =SUMPRODUCT(--(EXACT(TRIM(A1:A100), TRIM(F1)))). Além disso, certifique-se de que o cálculo esteja definido como Automático. Vá em Fórmulas > Opções de Cálculo e selecione Automático.
Desempenho muito lento com grandes conjuntos de dados
Fórmulas matriciais como SUMPRODUCT processam intervalos inteiros e podem ficar lentas com dezenas de milhares de linhas. Para melhorar o desempenho, faça referência apenas ao intervalo usado, em vez de colunas inteiras. Use A1:A10000 em vez de A:A. Considere usar uma coluna auxiliar com um sinalizador VERDADEIRO/FALSO simples e depois aplicar um COUNTIF regular nessa coluna, pois isso consome menos recursos para cálculos repetidos.
Comparação dos métodos de solução
| Item | SUMPRODUCT com EXACT | SUMPRODUCT com SEARCH | Coluna auxiliar com LEN |
|---|---|---|---|
| Uso principal | Correspondência exata com diferenciação de maiúsculas | Correspondência parcial sem diferenciação de maiúsculas | Identificar ou contar por comprimento de texto |
| Lida com texto >255 caracteres | Sim | Sim | Sim |
| Complexidade da fórmula | Moderada | Moderada | Baixa |
| Melhor para grandes conjuntos de dados | Pode ser lento | Pode ser lento | Mais rápido após configuração inicial |
| Facilidade de reutilização | Requer ajuste na fórmula | Requer ajuste na fórmula | Fácil de filtrar e referenciar |
Agora você pode contar com precisão células que contêm textos longos no Excel. Use o método SUMPRODUCT para uma correspondência exata ou parcial única. Implemente uma coluna auxiliar com a função LEN se precisar filtrar ou analisar dados repetidamente por comprimento de texto. Para tarefas relacionadas, explore o uso da função XLOOKUP para localizar valores de texto longos, pois ela não tem o limite de 255 caracteres na pesquisa. Lembre-se de que você pode combinar TRIM e CLEAN em suas fórmulas SUMPRODUCT para evitar erros causados por espaços ocultos ou caracteres não imprimíveis.