Contagem multi-condicional com condições complexas

Question

Eu tenho 4 colunas e muitas linhas. Preciso de uma fórmula para contar quantas linhas atendem aos seguintes critérios:

1. contém um determinado texto na coluna A
2. contém um determinado texto na coluna B
3. contém um valor de 0 na coluna C ou coluna D (se ambos forem 0 na mesma linha que contam como um resultado).

Exemplo: conta se a coluna A é "2015", coluna B é "Londres", coluna C ou coluna D é 0

    

Answer 1

Você pode usar SUMPRODUCT para fazer contagens condicionais. Embora a fórmula seja um pouco mais difícil de entender do que uma COUNTIFS , ela oferece muito mais flexibilidade nas condições de configuração. Aqui está uma fórmula de exemplo em que a tabela está em A1: D100.

=SUMPRODUCT((A1:A100="textA")*(B1:B100="textB")*(C1:C100*D1:D100=0))

Quebrando:
SUMPRODUCT somará uma matriz de valores. A matriz pode ser definida na função e não requer entrada de matriz especial.

Cada fator na expressão representa uma condição. Se uma condição for atendida, o valor do fator é 1. Se não for atendido, o fator será igual a 0. Como resultado, cada produto da matriz será igual a 1 ou 0. Aqueles que são iguais a 1 corresponde a linhas em que todas as condições são atendidas.

As condições:
(A1:A100="textA") Isso é verdade (1) se o valor da célula na coluna A for o texto "textA".

(B1:B100="textB") Isto é verdade (1) se o valor da célula na coluna B for o texto "textB".

(C1:C100*D1:D100=0) Isso usa um truque aritmético para fazer sua comparação OR. O produto de C e D será 0 somente se um dos dois (ou ambos) for igual a 0.

A soma de todos esses produtos é igual à sua contagem condicional.