Generisk formel
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Sammendrag
For å telle samsvarende rader med flere ELLER kriterier, kan du bruke en formel basert på SUMPRODUCT-funksjonen. I eksemplet vist er formelen i F10:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Denne formelen returnerer et antall rader der kolonne en er A eller B og kolonne to er X, Y eller Z.
Forklaring
Arbeidet fra innsiden og ut, og hvert kriterium blir brukt med en egen ISNUMBER + MATCH-konstruksjon. For å generere et antall rader i kolonne en der verdien er A eller B bruker vi:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH genererer et resultatarray som ser slik ut:
(1;2;#N/A;1;2;1;2)
og ISNUMBER konverterer denne matrisen til denne matrisen:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
For å generere et antall rader i kolonne to der verdien er X, Y eller Z bruker vi:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Så kommer MATCH tilbake:
(1;2;3;3;#N/A;1;2)
og ISNUMBER konverterer til:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Disse to matriser multipliseres sammen inne i SUMPRODUCT, som automatisk konverterer TRUE FALSE-verdier til 1 og 0 som en del av matteoperasjonen.
Så, for å visualisere, blir det endelige resultatet avledet slik:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Med cellehenvisninger
Eksemplet ovenfor bruker hardkodede matrisekonstanter, men du kan også bruke cellereferanser:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Flere kriterier
Denne tilnærmingen kan "skaleres opp" for å håndtere flere kriterier. Du kan se et eksempel i denne formelutfordringen.
