Som van de kleinste of onderste N waarden op basis van criteria in Excel

In een eerdere handleiding hebben we besproken hoe je de kleinste n waarden in een gegevensbereik kunt optellen. In dit artikel voeren we een verder gevorderde bewerking uit – het optellen van de laagste n waarden op basis van één of meerdere criteria in Excel.

Som van de kleinste of onderste N waarden op basis van criteria in Excel

Stel dat ik een bereik aan gegevens heb zoals in de onderstaande schermafbeelding te zien is, en nu wil ik de laagste 3 orders van het product Apple optellen.

In Excel kun je om de onderste n waarden in een bereik met criteria op te tellen een matrixformule maken door gebruik te maken van de functies SOM, KLEINSTE en ALS. De algemene syntaxis is:

• bereik=criteria: Het bereik aan cellen dat moet voldoen aan de specifieke criteria;
• waarden: De lijst die de onderste n waarden bevat die je wilt optellen;
• N: De N-de onderste waarde.

Om het bovenstaande probleem op te lossen, pas de onderstaande matrixformule toe in een lege cel:

Druk vervolgens tegelijkertijd op Ctrl + Shift + Enter om het juiste resultaat te krijgen zoals hieronder in de schermafbeelding te zien is:

Uitleg van de formule:

=SOM(KLEINSTE(ALS(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}))

• ALS(($A$2:$A$14=D2); $B$2:$B$14): Als het product in het bereik A2:A14 gelijk is aan “Apple”, dan wordt het bijbehorende getal uit de orderlijst (B2:B14) geretourneerd; als het product niet “Apple” is, wordt ONWAAR weergegeven. Je krijgt dan dit resultaat: {800;ONWAAR;ONWAAR;ONWAAR;1000;230;ONWAAR;ONWAAR;1600;ONWAAR;900;ONWAAR;500}.
• KLEINSTE(ALS(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}): Deze KLEINSTE-functie negeert de ONWAAR-waarden en retourneert de onderste 3 waarden in de array, dus het resultaat is dit: {230;500;800}.
• SOM(KLEINSTE(ALS(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}))=SOM({230;500;800}): Tenslotte sommeert de SOM-functie de getallen in de array om het resultaat te krijgen: 1530.

Tips: Omgaan met twee of meer voorwaarden:

Als je de onderste n waarden moet optellen op basis van twee of meer criteria, hoef je alleen maar andere bereiken en criteria toe te voegen met het *-teken binnen de ALS-functie zoals dit:

• Bereik1=criteria1: Het eerste bereik aan cellen dat moet voldoen aan de eerste criteria;
• Bereik2=criteria2: Het tweede bereik aan cellen dat moet voldoen aan de tweede criteria;
• Bereik3=criteria3: Het derde bereik aan cellen dat moet voldoen aan de derde criteria;
• waarden: De lijst die de onderste n waarden bevat die je wilt optellen;
• N: De N-de onderste waarde.

Bijvoorbeeld, ik wil de onderste 3 orders van het product Apple die verkocht zijn door Kerry optellen, pas dan de onderstaande formule toe:

Druk vervolgens tegelijkertijd op Ctrl + Shift + Enter om het resultaat te krijgen zoals je nodig hebt:

Gerelateerde functies gebruikt:

Meer artikelen: