Všeobecný vzorec
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Zhrnutie
Ak chcete počítať zodpovedajúce riadky s viacerými kritériami ALEBO, môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v F10:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Tento vzorec vráti počet riadkov, kde prvý stĺpec je A alebo B a druhý stĺpec je X, Y alebo Z.
Vysvetlenie
Pri práci zvnútra von sa každé kritérium aplikuje so samostatnou konštrukciou ISNUMBER + MATCH. Na vygenerovanie počtu riadkov v prvom stĺpci, kde je hodnota A alebo B, použijeme:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH generuje výsledné pole, ktoré vyzerá takto:
(1;2;#N/A;1;2;1;2)
a ISNUMBER prevádza toto pole na toto pole:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Na vygenerovanie počtu riadkov v stĺpci dva, kde hodnota je X, Y alebo Z, použijeme:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Potom sa MATCH vráti:
(1;2;3;3;#N/A;1;2)
a ISNUMBER konvertuje na:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Tieto dve polia sa vo vnútri SUMPRODUCT znásobia, čo automaticky prevedie TRUE FALSE hodnoty na 1 a 0 ako súčasť matematickej operácie.
Pre vizualizáciu je teda konečný výsledok odvodený 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 odkazmi na bunky
Vyššie uvedený príklad používa pevne zakomponované konštanty poľa, ale môžete tiež použiť odkazy na bunky:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Viac kritérií
Tento prístup je možné „zväčšiť“ tak, aby obsahoval viac kritérií. Príklad si môžete pozrieť v tejto vzorcovej výzve.
