Ladda ner exempelarbetsboken
Denna handledning kommer att visa hur man beräknar "sumproduct if", och returnerar summan av produkterna av arrays eller intervall baserat på kriterier.
SUMPRODUCT Funktion
SUMPRODUCT -funktionen används för att multiplicera matriser med tal och summera den resulterande matrisen.
För att skapa en "Sumproduct If" använder vi SUMPRODUCT -funktionen tillsammans med IF -funktionen i en matrisformel.
SUMPRODUCT IF
Genom att kombinera SUMPRODUCT och IF i en matrisformel kan vi i huvudsak skapa en "SUMPRODUCT IF" -funktion som fungerar ungefär som hur den inbyggda SUMIF-funktionen fungerar. Låt oss gå igenom ett exempel.
Vi har en lista över försäljningar som uppnåtts av mangers i olika regioner med motsvarande provision:
Antag att vi ombeds beräkna kommissionsbeloppet för varje chef så här:
För att åstadkomma detta kan vi häcka en IF -funktion med chef som våra kriterier inuti SUMPRODUCT -funktionen så här:
= SUMPRODUCT (OM (=,*))
= SUMPRODUCT (OM ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
När du använder Excel 2022 och tidigare måste du ange formeln genom att trycka på CTRL + SKIFT + ENTER för att få de lockiga parenteserna runt formeln (se översta bilden).
Hur fungerar formeln?
Formeln fungerar genom att utvärdera varje cell i vårt kriterieintervall som SANT eller FALSKT.
Beräknar den totala provisionen för Olivia:
= SUMPRODUCT (OM ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (OM ({SANT; SANT; FALSKT; FALSKT; SANT; FALSKT; FALSKT} FALSKT}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Därefter ersätter IF -funktionen varje värde med FALSE om dess villkor inte uppfylls.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Nu hoppar SUMPRODUCT -funktionen över de FALSE -värdena och summerar de återstående värdena (2.077,40).
SUMPRODUCT OM med flera kriterier
För att använda SUMPRODUCT IF med flera kriterier (liknande hur den inbyggda SUMIFS-funktionen fungerar), häck helt enkelt fler IF-funktioner i SUMPRODUCT-funktionen så här:
= SUMPRODUKT (OM (=, OM (=, *))
(CTRL + SKIFT + ENTER)
= SUMPRODUCT (OM ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SKIFT + ENTER)
En annan metod för SUMPRODUCT IF
Ofta i Excel finns det flera sätt att få fram önskade resultat. Ett annat sätt att beräkna "sumprodukt om" är att inkludera kriterierna inom SUMPRODUCT -funktionen som en array med dubbel unary så här:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Denna metod använder dubbel unary (-) för att konvertera en TRUE FALSE-array till nollor och enor. SUMPRODUCT multiplicerar sedan de konverterade kriterierna tillsammans:
= 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 och tricks:
- Om möjligt låsreferens (F4) alltid dina intervall och formelinmatningar för att tillåta automatisk fyllning.
- Om du använder Excel 2022 eller senare kan du ange formeln utan Ctrl + Skift + Retur.
SUMPRODUCT IF i Google Kalkylark
SUMPRODUCT IF -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
