Waarden optellen per groep in Excel

Soms heb je de behoefte om waarden op te tellen op basis van een groep in een tabel. Bijvoorbeeld, ik heb een lijst met producten met hun bijbehorende bedragen in een andere kolom, en nu wil ik het subtotaal voor elk product krijgen zoals in onderstaande schermafbeelding te zien is. Deze tutorial introduceert enkele formules om deze taak in Excel uit te voeren.

• Waarden optellen per groep – subtotalen in originele datatabel
• Waarden optellen per groep – subtotalen op een andere locatie

Waarden optellen per groep – subtotalen in originele datatabel

Hier is de algemene syntaxis om waarden op te tellen op basis van een groep in Excel:

• groepsnaam: De cel met de groepsnaam die je wilt optellen;
• cel_boven_groepsnaam: De cel boven de groepsnaam;
• groep_bereik: Het bereik van cellen dat de groepsnamen bevat;
• som_bereik: Het bereik van cellen dat overeenkomt met de opgegeven groepsnaam om samen op te tellen.

Als de gegevens gesorteerd zijn op de groeperingskolom, waarbij hetzelfde product samen staat zoals in onderstaande schermafbeelding te zien is, kun je voor het subtotale van gegevens per groep formules maken op basis van de ALS- en SOM.ALS-functies.

1. Kopieer en plak de volgende formule in een lege cel naast uw gegevens:

• Opmerking: In de formule:
• A1 is de kopcel, en A2 is de eerste cel die de productnaam bevat die u wilt gebruiken;
• A2:A13 is de lijst die de productnamen bevat waarop u wilt optellen;
• B2:B13 is de kolomgegevens waarvan u het subtotaal wilt verkrijgen.

2. Sleep vervolgens de vulgreep naar beneden naar de cellen waarin u deze formule wilt gebruiken, en de subtotalen worden berekend op basis van elke productnaam, zie onderstaande schermafbeelding:

Uitleg van de formule:

=ALS(A2=A1,"",SOM.ALS($A$2:$A$13,A2,$B$2:$B$13))

• SOM.ALS($A$2:$A$13,A2,$B$2:$B$13): Deze SOM.ALS-functie zal alleen de waarden in bereik B2:B13 optellen als hun overeenkomstige waarden in bereik A2:A13 gelijk zijn aan het criterium A2.
• ALS(A2=A1,"",SOM.ALS($A$2:$A$13,A2,$B$2:$B$13)): Deze ALS-functie controleert elke waarde in kolom A of deze waarde hetzelfde is als de waarde in de cel erboven. Bijvoorbeeld, als de celwaarde A2 gelijk is aan cel A1, wordt niets ( "" ) geretourneerd, zo niet, dan wordt het resultaat van de SOM.ALS-functie geretourneerd.

Waarden optellen per groep – subtotalen op een andere locatie

Als de waarden van elke groep niet samen staan en willekeurig in de kolom staan, om de overeenkomende waarden op te tellen op basis van groep of categorie, moet je elke groepsnaam uit de productnamen extraheren en vervolgens het subtotaal op basis van deze groepsnaam verkrijgen zoals in onderstaande schermafbeelding te zien is.

1. Extraheer eerst de unieke groepsnaam door de volgende matrixformule te gebruiken, druk vervolgens tegelijkertijd op Ctrl + Shift + Enter om het eerste resultaat te krijgen.

• Opmerking: In de formule:
• A2:A13 is het bereik van cellen waaruit alle unieke waarden moeten worden geëxtraheerd;
• D1 is de cel boven uw ingevoerde formule.

2. Selecteer vervolgens de formulecel en sleep de vulgreep naar beneden tot de cellen totdat alle productnamen worden weergegeven, zie onderstaande schermafbeelding:

3. Nu kunt u de waarden optellen op basis van de groepsnaam die u hebt geëxtraheerd. In dit geval wordt hier een SOM.ALS-functie gebruikt, voer de volgende formule in een lege cel in, in dit voorbeeld plaats ik deze in cel E2.

4. Sleep vervolgens de vulgreep naar beneden om deze formule te kopiëren en de totale bestelling van andere groepen te retourneren, zie onderstaande schermafbeelding:

Gerelateerde functie gebruikt:

Meer artikelen: