Obecný vzorec
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
souhrn
Chcete-li spočítat odpovídající řádky s více kritérii NEBO, můžete použít vzorec založený na funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v F10:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Tento vzorec vrací počet řádků, kde sloupec jeden je A nebo B a sloupec dva X, Y nebo Z.
Vysvětlení
Při práci zevnitř ven se každé kritérium aplikuje se samostatnou konstrukcí ISNUMBER + MATCH. Ke generování počtu řádků v prvním sloupci, kde je hodnota A nebo B, používáme:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH generuje výsledné pole, které vypadá takto:
(1;2;#N/A;1;2;1;2)
a ISNUMBER převede toto pole na toto pole:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Ke generování počtu řádků ve sloupci dva, kde je hodnota X, Y nebo Z, používáme:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Pak se MATCH vrátí:
(1;2;3;3;#N/A;1;2)
a ISNUMBER se převede na:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Tato dvě pole se v SUMPRODUCTu násobí dohromady, což automaticky převádí hodnoty TRUE FALSE na 1 a 0 jako součást matematické operace.
Takže pro vizualizaci je konečný výsledek odvozen takto:
=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 odkazy na buňky
Výše uvedený příklad používá napevno konstanty pole, ale můžete také použít odkazy na buňky:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Další kritéria
Tento přístup lze „zvětšit“, aby zvládl více kritérií. Příklad můžete vidět v této výzvě vzorce.
