Generieke formule
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Samenvatting
Om met meerdere criteria te tellen, inclusief logica voor NIET een van de verschillende dingen, kunt u de SUMPRODUCT-functie samen met de MATCH- en ISNA-functies gebruiken.
In het getoonde voorbeeld is de formule in G8:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Waar "geslacht" het benoemde bereik C4: C12 is, en "groep" het benoemde bereik D4: D12 is.
Opmerking: met MATCH en ISNA kan de formule gemakkelijk worden geschaald om meer uitsluitingen te verwerken, aangezien u het bereik gemakkelijk kunt uitbreiden met extra "NOT" -waarden.
Uitleg
De eerste uitdrukking in SUMPRODUCTS test waarden in kolom C, Geslacht, tegen de waarde in F4, "Mannelijk":
(gender=F4)
Het resultaat is een reeks TRUE FALSE-waarden zoals deze:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Waar waar overeenkomt met "Man".
De tweede uitdrukking binnen SUMPRODUCTS test waarden in kolom D, Groep, tegen de waarden in G4: G5, "A" en "B". Deze test wordt als volgt met MATCH en ISNA afgehandeld:
ISNA(MATCH(group,G4:G5,0))
De MATCH-functie wordt gebruikt om elke waarde in het benoemde bereik "groep" af te stemmen op waarden in G4: G5, "A" en "B". Waar de overeenkomst slaagt, retourneert VERGELIJKEN een getal. Waar de MATCH mislukt, geeft MATCH # N / A terug. Het resultaat is een array als deze:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Aangezien # N / A-waarden overeenkomen met "niet A of B", wordt ISNA gebruikt om de array "om te keren" naar:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Nu komt TRUE overeen met "niet A of B".
Binnen SUMPRODUCT worden de twee matrixresultaten met elkaar vermenigvuldigd, waardoor een enkele numerieke matrix ontstaat binnen SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SOMPRODUCT retourneert vervolgens de som, 2, die staat voor "2 mannen niet in groep A of B".
