Obecný vzorec
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
souhrn
Chcete-li počítat s více kritérii, včetně logiky pro NENÍ jedna z několika věcí, můžete použít funkci SUMPRODUCT společně s funkcemi MATCH a ISNA.
V zobrazeném příkladu je vzorec v G8:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Kde „pohlaví“ je pojmenovaný rozsah C4: C12 a „skupina“ je pojmenovaný rozsah D4: D12.
Poznámka: MATCH a ISNA umožňují vzorec snadno škálovat, aby zvládl více vyloučení, protože rozsah můžete snadno rozšířit tak, aby zahrnoval další hodnoty „NOT“.
Vysvětlení
První výraz uvnitř SUMPRODUCTS testuje hodnoty ve sloupci C, Pohlaví, proti hodnotě v F4, „Muž“:
(gender=F4)
Výsledkem je pole TRUE FALSE hodnot, jako je tato:
(PRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; PRAVDA; PRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA)
Kde TRUE odpovídá „Muž“.
Druhý výraz uvnitř SUMPRODUCTS testuje hodnoty ve sloupci D, Group, proti hodnotám v G4: G5, „A“ a „B“. Tento test je zpracován pomocí MATCH a ISNA takto:
ISNA(MATCH(group,G4:G5,0))
Funkce MATCH se používá k porovnání každé hodnoty v pojmenovaném rozsahu „skupina“ s hodnotami v G4: G5, „A“ a „B“. V případě úspěchu shody vrátí MATCH číslo. Pokud MATCH selže, MATCH vrátí # N / A. Výsledkem je pole jako toto:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Protože # N / A hodnoty odpovídají „ne A nebo B“, ISNA se používá k „obrácení“ pole na:
(NEPRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA)
Nyní TRUE odpovídá „ne A nebo B“.
Uvnitř SUMPRODUCT se výsledky dvou polí násobí dohromady, čímž se uvnitř SUMPRODUCT vytvoří jedno číselné pole:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT poté vrátí součet 2, což představuje „2 muži, kteří nejsou ve skupině A nebo B“.
