Generička formula
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Sažetak
Da biste računali s više kriterija, uključujući logiku za NIJE jednu od nekoliko stvari, možete koristiti funkciju SUMPRODUCT zajedno s funkcijama MATCH i ISNA.
U prikazanom primjeru formula u G8 je:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Gdje je "spol" imenovani raspon C4: C12, a "grupa" imenovani raspon D4: D12.
Napomena: MATCH i ISNA omogućuju da se formula lako skalira radi obrade više izuzetaka, jer možete lako proširiti raspon tako da uključuje dodatne vrijednosti "NOT".
Obrazloženje
Prvi izraz unutar SUMPRODUCTS testira vrijednosti u stupcu C, Spol, u odnosu na vrijednost u F4, "Muški":
(gender=F4)
Rezultat je niz TRUE FALSE vrijednosti poput ove:
(ISTINITO; NETOČNO; ISTINITO; NETOČNO; ISTINITO; ISTINITO; NETOČNO; TAČNO; NETOČNO)
Tamo gdje ISTINA odgovara "Muški".
Drugi izraz unutar SUMPRODUCTS testira vrijednosti u stupcu D, Grupa, naspram vrijednosti u G4: G5, "A" i "B". Ovaj test se obrađuje s MATCH i ISNA ovako:
ISNA(MATCH(group,G4:G5,0))
Funkcija MATCH koristi se za podudaranje svake vrijednosti u imenovanom rasponu "grupa" s vrijednostima u G4: G5, "A" i "B". Kada utakmica uspije, MATCH vraća broj. Tamo gdje MATCH ne uspije, MATCH vraća # N / A. Rezultat je niz poput ovog:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Budući da # N / A vrijednosti odgovaraju "ne A ili B", ISNA se koristi za "preokretanje" niza u:
(NETOČNO; NETOČNO; ISTINITO; NETOČNO; NETOČNO; TAČNO; NETOČNO; NETOČNO; ISTINITO)
Sada TRUE odgovara "ne A ili B".
Unutar SUMPRODUCT, dva rezultata niza množe se zajedno, što stvara jedan numerički niz unutar SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT zatim vraća zbroj 2, koji predstavlja "2 muškarca koji nisu u skupini A ili B".
