Contar células que contêm quer x quer y num intervalo no Excel

Para contar células que contêm quer um valor quer outro, alguns utilizadores do Excel recorrem à soma de duas funções CONTAR.SE. Contudo, este método aplica uma lógica E — contando apenas as células que contenham simultaneamente x e y — em vez da lógica OU pretendida (células que contenham x ou y). Para evitar contagens duplicadas e obter resultados precisos, este tutorial mostra-lhe, passo a passo, como utilizar uma fórmula baseada na função SOMARPRODUTO para contar, num intervalo específico, o número de células que contenham x ou y no Excel.

Como contar células que contêm quer x quer y num intervalo no Excel

Conforme ilustrado na imagem seguinte, existe um intervalo de dados B3:B9. Para contar o número de células neste intervalo que contenham «KTE» ou «KTO», aplique a fórmula abaixo e obtenha o resultado pretendido.

Fórmula genérica

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>,0))

Argumentos

Como utilizar esta fórmula?

1. Selecione uma célula vazia para exibir o resultado.

2. Introduza a fórmula abaixo nessa célula e prima a tecla Enter para obter o resultado.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>,0))

Como funcionam estas fórmulas?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>,0))

• 1. FIND(D3,B3:B9): A função PROCURAR verifica se o valor «KTE» em D3 existe no intervalo limitado (B3:B9) e devolve um vetor: {1,#VALOR!,#VALOR!,#VALOR!,#VALOR!,1,#VALOR!}.
  Neste vetor, os dois valores 1 indicam que a primeira e a penúltima células do intervalo B3:B9 contêm o valor «KTE»; os #VALOR! indicam que «KTE» não foi encontrado nas restantes células.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}A função É.NÚMERO devolve VERDADEIRO ao encontrar um número no vetor e FALSO ao encontrar erros, resultando no novo vetor {VERDADEIRO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO}.
• 3. FIND(D4,B3:B9)Esta função PROCURAR também verifica se o valor «KTO» em D4 existe no intervalo limitado (B3:B9) e devolve um vetor: {#VALOR!;1;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}A função É.NÚMERO devolve VERDADEIRO ao encontrar um número no vetor e FALSO ao encontrar erros, resultando no novo vetor {FALSO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;FALSO}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}Aqui, os dois vetores devolvem o resultado {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0Aqui compara-se se cada número no vetor é maior do que 0, devolvendo o resultado {VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE})Estes dois sinais de menos convertem «VERDADEIRO» em 1 e «FALSO» em 0, resultando num novo vetor {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1,1,0,0,0,1,0})A função SOMARPRODUTO soma todos os números no vetor e devolve o resultado final, que neste caso é 3.

Funções relacionadas

Fórmulas relacionadas