Általános képlet
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Összegzés
Ha több kritériummal szeretne számolni, beleértve a NEM a sok dolog logikáját, használhatja a SUMPRODUCT függvényt a MATCH és ISNA függvényekkel együtt.
A bemutatott példában a G8 képlete a következő:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Ahol a "nem" a C4: C12 elnevezett tartomány, a "csoport" pedig a D4: D12 elnevezett tartomány.
Megjegyzés: A MATCH és az ISNA lehetővé teszi a képlet méretezését, hogy kezelje a több kizárást, mivel könnyedén kibővítheti a tartományt további "NOT" értékekkel.
Magyarázat
A SUMPRODUCTS első kifejezése a C oszlop (Nem) értékeit teszteli az F4 "Férfi" értékével szemben:
(gender=F4)
Az eredmény egy IGAZ HAMIS értékek tömbje, mint ez:
(IGAZ; HAMIS; IGAZ; HAMIS; IGAZ; IGAZ; HAMIS; IGAZ; HAMIS)
Ahol az IGAZ megfelel a "Férfi" kifejezésnek.
A SUMPRODUCTS második kifejezése a D oszlop, Csoport oszlopban szereplő értékeket teszteli a G4-ben szereplő értékekkel: G5, "A" és "B". Ezt a tesztet a MATCH és az ISNA-val így kezelik:
ISNA(MATCH(group,G4:G5,0))
A MATCH függvény a "csoport" nevű tartomány minden értékének a G4: G5, "A" és "B" értékekhez való illesztésére szolgál. Ahol a mérkőzés sikeres, a MATCH számot ad vissza. Ahol a MATCH meghiúsul, a MATCH # N / A értéket ad vissza. Az eredmény egy ilyen tömb:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Mivel a # N / A érték megfelel a "nem A vagy B" -nek, az ISNA-t használják a tömb "megfordítására":
(HAMIS; HAMIS; IGAZ; HAMIS; HAMIS; IGAZ; HAMIS; HAMIS; IGAZ)
Most az IGAZ felel meg "nem A vagy B" -nek.
A SUMPRODUCT belsejében a két tömb eredményt megszorozza, ami egyetlen numerikus tömböt hoz létre a SUMPRODUCT belsejében:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
A SUMPRODUCT ezután visszaadja az 2 összeget, amely "2 olyan férfit jelent, amelyek nem tartoznak az A vagy B csoportba".
