Fórmula genérica
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Resumo
Para contar com vários critérios, incluindo lógica para NÃO uma de várias coisas, você pode usar a função SUMPRODUCT junto com as funções MATCH e ISNA.
No exemplo mostrado, a fórmula em G8 é:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Onde "gênero" é o intervalo nomeado C4: C12 e "grupo" é o intervalo nomeado D4: D12.
Nota: MATCH e ISNA permitem que a fórmula seja facilmente escalonada para lidar com mais exclusões, já que você pode facilmente expandir o intervalo para incluir valores "NÃO" adicionais.
Explicação
A primeira expressão dentro de SUMPRODUCTS testa os valores na coluna C, Sexo, em relação ao valor em F4, "Masculino":
(gender=F4)
O resultado é uma matriz de valores TRUE FALSE como este:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Onde TRUE corresponde a "Masculino".
A segunda expressão dentro de SUMPRODUCTS testa os valores na coluna D, Grupo, contra os valores em G4: G5, "A" e "B". Este teste é tratado com MATCH e ISNA assim:
ISNA(MATCH(group,G4:G5,0))
A função MATCH é usada para combinar cada valor no intervalo nomeado "grupo" com os valores em G4: G5, "A" e "B". Onde a correspondência é bem-sucedida, MATCH retorna um número. Onde o MATCH falha, MATCH retorna # N / A. O resultado é uma matriz como esta:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Como os valores # N / A correspondem a "não A ou B", ISNA é usado para "reverter" a matriz para:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Agora TRUE corresponde a "não A ou B".
Dentro de SUMPRODUCT, os dois resultados da matriz são multiplicados, o que cria uma única matriz numérica dentro de SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT então retorna a soma, 2, representando "2 machos que não estão no grupo A ou B".
