Functie SOMPRODUCT in Excel

Met de functie SOMPRODUCT vermenigvuldig je meerdere bereiken met elkaar, en laat Excel vervolgens de uitkomsten bij elkaar optellen.

Deze functie is handig omdat je hiermee meerdere berekeningen kunt combineren in één formule, waardoor je geen hulpkolom meer nodig hebt. Daarnaast kun je hiermee in combinatie met logische vergelijkingen formules maken waarmee je data kunt filteren binnen je berekening.

Schrijfwijze formule

=SOMPRODUCT(matrix1; [matrix2]; [matrix3]; …)

Argumenten

• Matrix1: Hier voer je eerste bereik met getallen in. Dit is verplicht.
• Matrix2: Hier kun je extra bereiken invoeren die je met matrix1 wilt vermenigvuldigen. Het is hierbij van belang dat alle bereiken even groot zijn.

Hoe deze functie werkt

Excel vermenigvuldigt met de functie SOMPRODUCT de waarden per rij met elkaar en telt alle uitkomsten bij elkaar op.

Stel je hebt een overzicht van bloemsoorten:

De traditionele aanpak om het totaal te berekenen zou zijn om een hulpkolom (D) toe te voegen waarbij je eerst per bloemsoort het totaal berekent (=B2*C2) en vervolgens kolom D bij elkaar op te tellen met =SOM(D2:D5).

Met de functie SOMPRODUCT kun je dit in één formule berekenen, dus zonder kolom D.

Dit werkt als volgt:

• Excel vermenigvuldigt eerst per rij:
  • 10 × 2,50 = 25
  • 15 × 1,20 = 18
  • 5 × 3,00 = 15
  • 8 x 1,00 = 8
• De uitkomsten worden daarbij automatisch bij elkaar opgeteld:
  • 25 + 18 + 15 + 8 = 66

Voorbeeld met logische vergelijkingen

Nu je de basis weet van hoe deze functie werkt, gaan we het krachtiger maken door het te combineren met logische voorwaarden.

Stel dat je een overzicht hebt van telefoons met daarin het merk, de prijs per stuk en de voorraad.

We willen nu de totale voorraadwaarde van alle telefoons van Apple berekenen.

De formule hiervoor is =SOMPRODUCT((A2:A6="Apple")*(C2:C6)*(D2:D6))

Dit werkt als volgt:

• Deze formule heeft de logische vergelijking =A2:A6="Apple"
  • Excel controleert hiermee per rij of het merk gelijk is aan (=) Apple.
  • Dit resulteert in dit voorbeeld in WAAR, WAAR, ONWAAR, WAAR, ONWAAR.
  • Wanneer Excel deze waarden vermenigvuldigt, zet het WAAR automatisch om in 1 en ONWAAR in 0. In dit voorbeeld krijg je daardoor 1, 1, 0, 1, 0.
• Excel vermenigvuldigt nu de rijen met elkaar
  • Rijen met ONWAAR krijgt nu de vermenigvuldiging 0 (ONWAAR) x aantal x prijs per stuk = altijd 0
  • Rijen met WAAR (in dit geval dus Apple) krijgen de vermenigvuldiging 1 (WAAR) x aantal x prijs per stuk.
• De functie SOMPRODUCT telt tenslotte alles bij elkaar op

Om te controleren of het klopt, en om te laten zien wat Excel doet, heb ik de merknamen verandert in 1 (Apple) en 0 (Samsung) en in kolom E een hulpkolom toegevoegd:

Wanneer vervolgens kolom E bij elkaar wordt opgeteld, zie je dat het resultaat hetzelfde is als de formule met SOMPRODUCT en de logische voorwaarde:

Je kunt de formule eventueel nog dynamischer maken, door de vergelijking “Apple” te vervangen door een celverwijzing (bijv. B8):

Je kunt op deze manier eenvoudig voorwaarden toepassen op je berekeningen, waardoor je dus kunt filteren en rekeningen tegelijk.