Yleinen kaava
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Yhteenveto
Voit laskea yhteensopivia rivejä, joissa on useita TAI-ehtoja, käyttämällä kaavaa, joka perustuu SUMPRODUCT-funktioon. Esitetyssä esimerkissä kaava kohdassa F10 on:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Tämä kaava palauttaa rivien määrän, joissa sarake yksi on A tai B ja sarake kaksi on X, Y tai Z.
Selitys
Työskentelemällä sisältä ulospäin, kutakin kriteeriä sovelletaan erillisellä ISNUMBER + MATCH -rakenteella. Luodaksemme rivien määrän sarakkeessa, jonka arvo on A tai B, käytämme:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH luo tulostaulukon, joka näyttää tältä:
(1;2;#N/A;1;2;1;2)
ja ISNUMBER muuntaa tämän taulukon tälle taulukolle:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Luodaksemme rivien määrän sarakkeessa 2, joiden arvo on X, Y tai Z, käytämme:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Sitten MATCH palaa:
(1;2;3;3;#N/A;1;2)
ja ISNUMBER muuntaa seuraaviksi:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Nämä kaksi taulukkoa kerrotaan yhdessä SUMPRODUCT: n sisällä, joka muuntaa TOSI EPÄTOSI -arvot automaattisesti arvoksi 1 ja 0 matemaattisen operaation osana.
Joten visualisoimiseksi lopputulos saadaan näin:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Soluviittauksilla
Yllä olevassa esimerkissä käytetään kovakoodattuja matriisivakioita, mutta voit käyttää myös soluviittauksia:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Lisää kriteerejä
Tätä lähestymistapaa voidaan "laajentaa" käsittelemään enemmän kriteerejä. Näet esimerkin tässä kaavahaasteessa.
