Generisk formel
=SUMPRODUCT(--(range1=criteria),range2)
Sammendrag
For å summere eller delsum kolonner basert på kriterier i tilstøtende kolonner, kan du bruke en formel basert på SUMPRODUCT-funksjonen. I eksemplet vist er formelen i J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Forklaring
I kjernen bruker denne formelen SUMPRODUCT til å multiplisere og summere produkter av to matriser: array1 og array2 . Den første matrisen, array1, er satt opp til å fungere som et "filter" for å tillate bare verdier som oppfyller kriteriene.
Array1 bruker et område som begynner i den første kolonnen som inneholder verdier som må oppfylle kriteriene. Disse "kriterieverdiene" sitter i en kolonne til venstre for og rett ved siden av "dataverdiene".
Kriteriene brukes som en enkel test som skaper en rekke SANNE og FALSE verdier:
--($B5:$H5=J$4)
Denne biten av formelen "tester" hver verdi i den første matrisen ved å bruke kriteriene som er oppgitt, og bruker deretter en dobbelt-negativ (-) for å tvinge de resulterende SANNE og FALSE verdiene til 1 og 0. Resultatet ser slik ut:
(1,0,0,0,1,0,1)
Merk at 1s tilsvarer kolonne 1,5 og 7, som oppfyller kriteriene for "A".
For array2 inne i SUMPRODUCT bruker vi et område som "forskyves" med en kolonne til høyre. Dette området starter med at den første kolonnen inneholder verdier som skal summeres, og slutter med den siste kolonnen som inneholder verdier som skal summeres.
Så i eksempelformelen i J5, etter at matriser er fylt ut, har vi:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Siden SUMPRODUCT er programmert spesielt for å ignorere feilene som skyldes multiplisering av tekstverdier, ser den endelige matrisen slik ut:
(1,0,0,0,1,0,1)
De eneste verdiene som "overlever" multiplikasjon er de som tilsvarer 1s i array1 . Du kan tenke på logikken i array1 "filtrere" verdiene i array2 .
