Vispārēja formula
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Kopsavilkums
Lai saskaitītu atbilstošās rindas ar vairākiem VAI kritērijiem, varat izmantot formulu, kuras pamatā ir funkcija SUMPRODUCT. Parādītajā piemērā formula F10 ir:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Šī formula atgriež rindu skaitu, kur pirmā kolonna ir A vai B, bet otrā kolonna ir X, Y vai Z.
Paskaidrojums
Strādājot no iekšpuses uz āru, katrs kritērijs tiek piemērots ar atsevišķu ISNUMBER + MATCH konstrukciju. Lai izveidotu rindu skaitu pirmajā kolonnā, kuras vērtība ir A vai B, mēs izmantojam:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH ģenerē rezultātu masīvu, kas izskatās šādi:
(1;2;#N/A;1;2;1;2)
un ISNUMBER pārveido šo masīvu par šo masīvu:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Lai izveidotu rindu skaitu otrajā kolonnā, kur vērtība ir X, Y vai Z, mēs izmantojam:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Tad atgriežas MATCH:
(1;2;3;3;#N/A;1;2)
un ISNUMBER pārveido par:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Šie divi masīvi tiek reizināti kopā SUMPRODUCT iekšpusē, kas matemātikas darbības ietvaros automātiski pārvērš TRUE FALSE vērtības uz 1 un 0.
Tātad, lai vizualizētu, gala rezultāts tiek iegūts šādi:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Ar šūnu atsaucēm
Iepriekš minētajā piemērā tiek izmantotas stingri kodētas masīvu konstantes, taču varat izmantot arī šūnu atsauces:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Vairāk kritēriju
Šo pieeju var "palielināt", lai apstrādātu vairāk kritēriju. Šajā formulas izaicinājumā varat redzēt piemēru.
