Excel SUMPRODUCT-functie

In Excel kunt u de SOMPRODUCT-functie gebruiken om twee of meer kolommen of matrices met elkaar te vermenigvuldigen en vervolgens de som van de producten te berekenen. SOMPRODUCT is een krachtige functie die, net als AANTAL.ALS en SOM.ALS, celwaarden kan tellen of optellen op basis van meerdere criteria. In dit artikel bespreken we de syntaxis van de functie en enkele praktische voorbeelden van SOMPRODUCT.

• Voorbeeld 1: Basisgebruik van de SOMPRODUCT-functie
• Voorbeeld 2: Cellen optellen met meerdere criteria met de SOMPRODUCT-functie
• Voorbeeld 3: Cellen tellen met meerdere criteria met de SOMPRODUCT-functie
• Voorbeeld 4: Het gewogen gemiddelde berekenen met de SOMPRODUCT-functie

 Syntaxis:

De syntaxis voor de SOMPRODUCT-functie in Excel is:

 Argumenten:

• array1: Vereist. Het eerste matrix- of celbereik dat u wilt vermenigvuldigen en daarna optellen.
• array2: Optioneel. De tweede matrix of het celbereik dat u wilt vermenigvuldigen en daarna optellen.

Opmerkingen:

• 1. Als er niet-numerieke waarden in uw bereik staan, behandelt SOMPRODUCT deze als nul.
• 2. Als de matrices of bereiken niet hetzelfde aantal rijen en kolommen bevatten, retourneert de SOMPRODUCT-functie een #WAARDE!-fout.
• 3. Als de matrices logische tests bevatten, leveren deze WAAR- en ONWAAR-waarden op. In de meeste gevallen converteert u deze naar 1 en 0 met de dubbele unaire operator (--).
• 4. De SOMPRODUCT-functie ondersteunt tot 255 argumenten in Excel 2007 en nieuwere versies, maar slechts 30 argumenten in oudere Excel-versies.
• 5. De SOMPRODUCT-functie ondersteunt geen jokertekens.

 Resultaat:

Geeft het resultaat van de vermenigvuldigde en opgetelde matrices.

 Voorbeelden:

Voorbeeld 1: Basisgebruik van de SOMPRODUCT-functie

Het basisgebruik van SOMPRODUCT is het vermenigvuldigen van twee kolommen en deze daarna optellen. Bijvoorbeeld: u hebt de gegevens zoals in de onderstaande schermafbeelding en u wilt de bestelling vermenigvuldigen met de eenheidsprijs en vervolgens alle vermenigvuldigingsresultaten optellen om de totale prijs te verkrijgen.

Om deze taak uit te voeren, past u de volgende formule toe:

Uitleg: Deze formule SOMPRODUCT(B2:B7;C2:C7) = B2*C2 + B3*C3 + B4*C4 + B5*C5 + B6*C6 + B7*C7.

Druk vervolgens op Enter om de cellen in kolom B te vermenigvuldigen met de overeenkomstige cellen in dezelfde rij van kolom C en de resultaten op te tellen. Zie schermafbeelding:

Voorbeeld 2: Cellen optellen met meerdere criteria met de SOMPRODUCT-functie

Naast de SOM.ALS-functie voor het optellen van celwaarden op basis van meerdere voorwaarden, kunt u deze taak ook snel en eenvoudig uitvoeren met de SOMPRODUCT-functie.

Geval 1: Celwaarden optellen als een andere kolom specifieke tekst bevat

Stel dat u een gegevenstabel hebt zoals hieronder weergegeven. Hoe berekent u met de SOMPRODUCT-formule in Excel de totale prijs voor product KTE en naam David?

1. Voer een van de onderstaande formules in een lege cel in of kopieer deze:

• Tips: In de bovenstaande formule:
• A2:A12=„KTE": is het eerste criteriabereik en de bijbehorende criteria waarmee u wilt optellen.
• B2:B12=„David": is het tweede criteriumgebied en het bijbehorende criterium waarop u wilt optellen.
• D2:D12: is het bereik met celwaarden dat u wilt optellen.

2. Druk vervolgens op Enter om het gewenste resultaat te verkrijgen:

Geval 2: Cellen optellen met meerdere criteria met OF-logica

Om cellen te tellen op basis van meerdere criteria met OF-logica, combineert u de voorwaarden binnen de SOMPRODUCT-functie met het plusteken (+).

Bijvoorbeeld: ik wil de totale prijs van het product KTE en KTO in kolom A optellen, zoals hieronder weergegeven:

Pas de volgende formule toe in een lege cel:

Druk vervolgens op Enter om de totale prijs van het product KTE en KTO te berekenen. Zie schermafbeelding:

Geval 3: Cellen optellen met meerdere criteria met OF- en EN-logica

In sommige situaties moet u cellen optellen met zowel OF- als EN-logica tegelijk — en ook die taak is eenvoudig uit te voeren met de SOMPRODUCT-functie.

Sterretje (*) wordt gebruikt als EN-operator.

Het plusteken (+) wordt gebruikt als OF-operator.

Laten we een voorbeeld bekijken: de totale prijs optellen van de producten KTE en KTO wanneer de verkoop hoger is dan 200.

Kopieer of voer de onderstaande formule in:

Druk vervolgens op Enterom het gewenste resultaat te verkrijgen:

Voorbeeld 3: Cellen tellen met meerdere criteria met de SOMPRODUCT-functie

Over het algemeen helpt de SOMPRODUCT-functie ons ook bij het tellen van cellen die aan meerdere criteria voldoen in Excel. Net als bij het optellen van waarden, hoeft u alleen het optelbereik-argument uit de formule te verwijderen.

Geval 1: Cellen tellen met meerdere criteria met EN-logica

Hier wil ik de cellen tellen waarin het product KTE is en de verkoop hoger dan 200 bedraagt. Gebruik daarom een van de onderstaande formules:

• Tips: In de bovenstaande formule:
• A2:A12=„KTE": is het eerste bereik met criteria en de voorwaarde waaraan u wilt tellen.
• B2:B12>200: is het tweede criteriumgebied en het criterium waaraan u wilt tellen.

Druk vervolgens op Enterom het aantal resultaten te verkrijgen:

Geval 2: Cellen tellen met meerdere criteria met OF-logica

Om het aantal producten KTE en KTO in kolom A te tellen, past u deze formule toe:

Druk vervolgens op Enter om het aantal producten KTE en KTO te verkrijgen. Zie schermafbeelding:

Geval 3: Cellen tellen met meerdere criteria met OF- en EN-logica

Om het aantal producten KTE en KTO te tellen waarbij de verkoop groter is dan 200, past u de volgende formule toe:

Druk vervolgens op Enterom het gewenste aantal resultaten te verkrijgen:

Voorbeeld 4: Het gewogen gemiddelde berekenen met de SOMPRODUCT-functie

Normaal gesproken kunnen we het gemiddelde van een reeks cellen snel en eenvoudig berekenen. Maar als u het gemiddelde van een lijst met waarden wilt berekenen die verschillende mate van relevantie hebben – oftewel het gewogen gemiddelde – is er hiervoor geen directe functie beschikbaar in Excel. De SOMPRODUCT-functie kan u hier echter wel bij helpen.

Bijvoorbeeld: ik heb een cijferoverzicht van een student, waarbij elke opdracht een ander wegingsfactor heeft, zoals hieronder wordt weergegeven.

Gebruik de volgende formule om het gewogen gemiddelde te berekenen:

Uitleg: Deze formule:SOMPRODUCT(B2:B6; C2:C6) / SOM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

Druk daarna op de toets Enter, dan wordt het gewogen gemiddelde direct berekend, zie schermafbeelding:

 Meer artikelen met de functie SOMPRODUCT: