Generička formula
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Sažetak
Za brojanje podudarnih redaka s višestrukim ILI kriterijima možete koristiti formulu koja se temelji na funkciji SUMPRODUCT. U prikazanom primjeru formula u F10 je:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Ova formula vraća broj redaka u kojima je prvi stupac A ili B, a drugi stupac X, Y ili Z.
Obrazloženje
Radeći iznutra prema van, svaki se kriterij primjenjuje zasebnom konstrukcijom ISNUMBER + MATCH. Za generiranje broja redaka u prvom stupcu gdje je vrijednost A ili B koristimo:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH generira rezultatski niz koji izgleda ovako:
(1;2;#N/A;1;2;1;2)
a ISNUMBER pretvara ovaj niz u ovaj niz:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Za generiranje broja redaka u stupcu dva gdje je vrijednost X, Y ili Z koristimo:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Tada se MATCH vraća:
(1;2;3;3;#N/A;1;2)
i ISNUMBER pretvara u:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Ova dva polja množe se zajedno unutar SUMPRODUCT-a, koji automatski pretvara TRUE FALSE vrijednosti u 1 i 0 kao dio matematičke operacije.
Dakle, za vizualizaciju, konačni rezultat izveden je ovako:
=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 referencama na ćelije
Gornji primjer koristi čvrsto kodirane konstante niza, ali možete koristiti i reference ćelija:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Više kriterija
Ovaj se pristup može "povećati" kako bi se moglo podnijeti više kriterija. Možete vidjeti primjer u ovom izazovu formule.
