Download eksemplet på projektmappe
Denne vejledning demonstrerer, hvordan man beregner "sumproduct if", og returnerer summen af produkterne af arrays eller intervaller baseret på kriterier.
SUMPRODUCT Funktion
SUMPRODUCT -funktionen bruges til at multiplicere rækker af tal, der summerer det resulterende array.
For at oprette et "Sumproduct If" vil vi bruge SUMPRODUCT -funktionen sammen med IF -funktionen i en matrixformel.
SUMPRODUCT IF
Ved at kombinere SUMPRODUCT og IF i en matrixformel kan vi i det væsentlige oprette en “SUMPRODUCT IF” -funktion, der fungerer på samme måde som den indbyggede SUMIF-funktion. Lad os gå gennem et eksempel.
Vi har en liste over salg, der er opnået af forvaltere i forskellige regioner med tilsvarende provision:
Antages, at vi bliver bedt om at beregne kommissionsbeløbet for hver leder således:
For at opnå dette kan vi indlejre en IF -funktion med Manager som vores kriterier inde i SUMPRODUCT -funktionen sådan:
= SUMPRODUCT (HVIS (=,*))
= SUMPRODUCT (HVIS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Når du bruger Excel 2022 og tidligere, skal du indtaste formlen ved at trykke på CTRL + SKIFT + ENTER for at få de krøllede parenteser omkring formlen (se øverste billede).
Hvordan fungerer formlen?
Formlen fungerer ved at evaluere hver celle i vores kriterieområde som SAND eller FALSK.
Beregning af den samlede provision for Olivia:
= SUMPRODUCT (HVIS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUKT (HVIS ({SAND; SAND; FALSK; FALSK; SAND; FALSK; FALSK} FALSK}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Dernæst erstatter IF -funktionen hver værdi med FALSK, hvis betingelsen ikke er opfyldt.
= SUMPRODUCT ({928.62; 668.22; FALSK; FALSK; FALSK; 480.564; FALSK; FALSK; FALSK})
Nu springer SUMPRODUCT -funktionen de FALSKE værdier over og summerer de resterende værdier (2.077,40).
SUMPRODUCT IF med flere kriterier
Hvis du vil bruge SUMPRODUCT IF med flere kriterier (svarende til hvordan den indbyggede SUMIFS-funktion fungerer), skal du simpelthen nest flere IF-funktioner i SUMPRODUCT-funktionen sådan:
= SUMPRODUCT (HVIS (=, HVIS (=, *))
(CTRL + SKIFT + ENTER)
= SUMPRODUCT (HVIS ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SKIFT + ENTER)
En anden tilgang til SUMPRODUCT IF
Ofte i Excel er der flere måder at udlede til de ønskede resultater. En anden måde at beregne “sumproduct if” på er at inkludere kriterierne inden for SUMPRODUCT -funktionen som en array ved hjælp af dobbelt unary som sådan:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Denne metode bruger den dobbelte unary (-) til at konvertere et TRUE FALSE-array til nuller og dem. SUMPRODUCT multiplicerer derefter de konverterede kriterierarrays sammen:
= SUMPRODUKT ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Tips og tricks:
- Hvor det er muligt, skal du altid låse-reference (F4) dine intervaller og formelindgange for at tillade automatisk udfyldning.
- Hvis du bruger Excel 2022 eller nyere, kan du indtaste formlen uden Ctrl + Shift + Enter.
SUMPRODUCT IF i Google Sheets
SUMPRODUCT IF -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
