Bendroji formulė
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Santrauka
Norėdami suskaičiuoti atitinkamas eilutes su keliais ARBA kriterijais, galite naudoti formulę, pagrįstą funkcija SUMPRODUCT. Parodytame pavyzdyje formulė F10 yra:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Ši formulė pateikia eilučių skaičių, kai pirmasis stulpelis yra A arba B, o antrasis stulpelis yra X, Y arba Z.
Paaiškinimas
Dirbant iš vidaus, kiekvienas kriterijus taikomas su atskira ISNUMBER + MATCH konstrukcija. Norėdami sukurti eilučių skaičių pirmame stulpelyje, kurio vertė yra A arba B, mes naudojame:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH sukuria rezultatų masyvą, kuris atrodo taip:
(1;2;#N/A;1;2;1;2)
ir ISNUMBER konvertuoja šį masyvą į šį masyvą:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Norėdami sukurti eilučių skaičių antrame stulpelyje, kurio vertė yra X, Y arba Z, mes naudojame:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Tada MATCH grįžta:
(1;2;3;3;#N/A;1;2)
ir ISNUMBER konvertuojasi į:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Šie du masyvai yra dauginami kartu SUMPRODUCT viduje, kuris automatiškai konvertuoja TRUE FALSE reikšmes į 1 ir 0 kaip matematikos operacijos dalį.
Taigi, norint vizualizuoti, galutinis rezultatas gaunamas taip:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Su langelių nuorodomis
Aukščiau pateiktame pavyzdyje naudojamos kietojo kodo masyvo konstantos, tačiau taip pat galite naudoti langelių nuorodas:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Daugiau kriterijų
Šis požiūris gali būti „padidintas“, kad būtų laikomasi daugiau kriterijų. Šios formulės iššūkyje galite pamatyti pavyzdį.
