Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du beregner "sumproduct if", og returnerer summen av produktene av arrays eller områder basert på kriterier.
SUMPRODUCT Funksjon
SUMPRODUCT -funksjonen brukes til å multiplisere matriser med tall, som summerer den resulterende matrisen.
For å lage et "Sumproduct If", bruker vi SUMPRODUCT -funksjonen sammen med IF -funksjonen i en matriseformel.
SUMPRODUCT IF
Ved å kombinere SUMPRODUCT og IF i en matriseformel, kan vi i hovedsak lage en “SUMPRODUCT IF” -funksjon som fungerer omtrent som den innebygde SUMIF-funksjonen. La oss gå gjennom et eksempel.
Vi har en liste over salg oppnådd av forvaltere i forskjellige regioner med tilsvarende provisjonspriser:
Anta at vi blir bedt om å beregne provisjonsbeløpet for hver leder slik:
For å oppnå dette kan vi hekke en IF -funksjon med sjef som våre kriterier inne i SUMPRODUCT -funksjonen slik:
= SUMPRODUCT (HVIS (=,*))
= SUMPRODUCT (HVIS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Når du bruker Excel 2022 og tidligere, må du angi formelen ved å trykke CTRL + SKIFT + ENTER for å få de krøllete parentesene rundt formelen (se øverste bilde).
Hvordan fungerer formelen?
Formelen fungerer ved å evaluere hver celle i våre kriterier som SANN eller FALSK.
Beregning av den totale provisjonen for Olivia:
= SUMPRODUCT (HVIS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUKT (HVIS ({TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE} FALSE}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Deretter erstatter IF -funksjonen hver verdi med FALSE hvis betingelsen ikke er oppfylt.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Nå hopper SUMPRODUCT -funksjonen over FALSE -verdiene og summerer de resterende verdiene (2.077,40).
SUMPRODUCT IF med flere kriterier
Hvis du vil bruke SUMPRODUCT IF med flere kriterier (ligner på hvordan den innebygde SUMIFS-funksjonen fungerer), legger du bare inn flere IF-funksjoner i SUMPRODUCT-funksjonen slik:
= 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 annen tilnærming til SUMPRODUCT IF
Ofte i Excel er det flere måter å utlede de ønskede resultatene. En annen måte å beregne “sumproduct if” på er å inkludere kriteriene innenfor SUMPRODUCT -funksjonen som en matrise som bruker dobbel unary slik:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Denne metoden bruker dobbel unary (-) til å konvertere en TRUE FALSE-matrise til nuller og ener. SUMPRODUCT multipliserer deretter de konverterte kriteriene med hverandre:
= 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 triks:
- Når det er mulig, må du alltid låse-referanse (F4) dine områder og formelinnganger for å tillate automatisk fylling.
- Hvis du bruker Excel 2022 eller nyere, kan du skrive inn formelen uten Ctrl + Shift + Enter.
SUMPRODUCT IF i Google Regneark
SUMPRODUCT IF -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
