Generisk formel
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Sammendrag
For å summere verdier i samsvarende kolonner og rader, kan du bruke SUMPRODUCT-funksjonen. I eksemplet vist er formelen i J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
der data (C5: G14), dager (B5: B14) og koder (C4: G4) er kalt områder.
Forklaring
SUMPRODUCT-funksjonen kan håndtere matriser naturlig, uten at det kreves kontrollskift.
I dette tilfellet multipliserer vi alle verdiene i de nevnte områdedataene med to uttrykk som filtrerer ut verdier som ikke er av interesse. Det første uttrykket bruker et filter basert på koder:
(codes=J4)
Siden J4 inneholder "A002", oppretter uttrykket en matrise med SANNE FALSE verdier som dette:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Det andre uttrykket filtrerer på dagen:
(days=J5)
Siden J4 inneholder "ons", oppretter uttrykket en matrise med SANNE FALSE verdier som dette:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
I Excel blir TRUE FALSE-verdier automatisk tvunget til 1 og 0-verdier av en hvilken som helst matematikkoperasjon, så multiplikasjonsoperasjonen tvinger matriser over til en og nuller, og skaper en 2D-matrise med samme dimensjoner som de opprinnelige dataene. Prosessen kan visualiseres som vist nedenfor:
Til slutt returnerer SUMPRODUCT summen av alle elementene i den endelige matrisen, 9.
Tell i stedet for sum
Hvis du vil telle samsvarende verdier i stedet for å summere, kan du forkorte formelen til:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Merk at denne tellingen inkluderer tomme celler.
Merknader
- Selv om eksemplet bare viser en samsvarende kolonne, vil denne formelen oppsummere flere matchede kolonner riktig.
- Hvis du bare trenger å matche kolonner (ikke rader), kan du bruke en formel som denne.
- For å bare matche rader, kan du bruke COUNTIFS-funksjonen.
