Fórmula genérica
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Resumen
Para contar con varios criterios, incluida la lógica para NO una de varias cosas, puede usar la función SUMPRODUCT junto con las funciones MATCH e ISNA.
En el ejemplo que se muestra, la fórmula en G8 es:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Donde "género" es el rango con nombre C4: C12 y "grupo" es el rango con nombre D4: D12.
Nota: MATCH e ISNA permiten que la fórmula se escale fácilmente para manejar más exclusiones, ya que puede expandir fácilmente el rango para incluir valores "NOT" adicionales.
Explicación
La primera expresión dentro de SUMPRODUCTS compara los valores de la columna C, Sexo, con el valor de F4, "Hombre":
(gender=F4)
El resultado es una matriz de valores VERDADERO FALSO como este:
(VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO)
Donde VERDADERO corresponde a "Hombre".
La segunda expresión dentro de SUMPRODUCTS prueba los valores en la columna D, Grupo, contra los valores en G4: G5, "A" y "B". Esta prueba se maneja con MATCH e ISNA de esta manera:
ISNA(MATCH(group,G4:G5,0))
La función COINCIDIR se utiliza para hacer coincidir cada valor en el rango denominado "grupo" con los valores en G4: G5, "A" y "B". Cuando la coincidencia tiene éxito, MATCH devuelve un número. Cuando el PARTIDO falla, PARTIDO devuelve # N / A. El resultado es una matriz como esta:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Dado que los valores # N / A corresponden a "ni A ni B", ISNA se utiliza para "invertir" la matriz a:
(FALSO; FALSO; VERDADERO; FALSO; FALSO; VERDADERO; FALSO; FALSO; VERDADERO)
Ahora VERDADERO corresponde a "ni A ni B".
Dentro de SUMPRODUCT, los dos resultados de la matriz se multiplican, lo que crea una única matriz numérica dentro de SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT luego devuelve la suma, 2, que representa "2 hombres que no están en el grupo A o B".
