Fórmula genérica
=SUMPRODUCT(--NOT(ISERROR(range)))
Resumo
Para contar o número de células que não contêm erros, você pode usar as funções ISERROR e NOT, agrupadas na função SUMPRODUCT. No exemplo mostrado, a fórmula em E5 é:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Explicação
Neste exemplo, o objetivo é contar o número de células em um intervalo que não contém erros.
Trabalhando de dentro para fora, primeiro usamos a função ISERROR em todo o intervalo:
ISERROR(B5:B14) // check all 10 cells
Como há dez células no intervalo B5: B14, ISERROR retorna uma matriz com dez resultados como este:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Aqui, cada valor TRUE indica um valor de célula que é um erro. Como o objetivo é contar células que não contêm erros, invertemos esses resultados com a função NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
que retorna:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Observe que cada valor TRUE agora corresponde a uma célula que não contém um erro. Esta matriz agora está no formato correto - valores TRUE indicam células sem erros, valores FALSE indicam células com erros.
Como o SUMPRODUCT só funciona com dados numéricos, a próxima etapa é converter os valores TRUE e FALSE em seus equivalentes numéricos, 1 e 0. Fazemos isso com um negativo duplo (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
A matriz resultante é semelhante a esta:
(1;0;1;1;1;0;1;1;1;0)
Por fim, SUMPRODUCT soma os itens desta matriz e retorna o total, que no exemplo é o número 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Função ISERR
Como a função ISERROR, a função ISERR retorna TRUE quando um valor é um erro. A diferença é que ISERR ignora erros # N / A. Se você deseja contar células que não contêm erros e ignorar erros # N / A, pode substituir ISERR por ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Opção SUM
Você também pode usar a função SUM para contar erros. A estrutura da fórmula é a mesma:
=SUM(--NOT(ISERROR(B5:B14)))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
