Splošna formula
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Povzetek
Če želite šteti ujemajoče se vrstice z več kriteriji ALI, lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v F10:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Ta formula vrne število vrstic, kjer je stolpec ena A ali B, drugi stolpec pa X, Y ali Z.
Pojasnilo
Vsako merilo se uporablja od znotraj navzven z ločeno konstrukcijo ISNUMBER + MATCH. Za generiranje števila vrstic v prvem stolpcu, kjer je vrednost A ali B, uporabimo:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH ustvari matriko rezultatov, ki je videti takole:
(1;2;#N/A;1;2;1;2)
in ISNUMBER pretvori to polje v to polje:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Za ustvarjanje števila vrstic v stolpcu dva, kjer je vrednost X, Y ali Z, uporabimo:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Nato vrne MATCH:
(1;2;3;3;#N/A;1;2)
in ISNUMBER pretvori v:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Ta dva polja se pomnožijo znotraj SUMPRODUCT, ki samodejno pretvori TRUE FALSE vrednosti v 1 in 0 kot del matematične operacije.
Torej, za vizualizacijo končni rezultat izpeljemo takole:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
S sklici na celice
Zgornji primer uporablja trdno kodirane konstante matrike, lahko pa tudi sklice na celice:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Več meril
Ta pristop je mogoče "razširiti" tako, da obravnava več meril. Primer lahko vidite v tem izzivu formule.
