Sommeer als cellen specifieke tekst bevatten in een andere kolom

In deze tutorial wordt uitgelegd hoe je waarden kunt optellen als cellen specifieke of gedeeltelijke tekst bevatten in een andere kolom. Neem de onderstaande gegevensreeks als voorbeeld om het totaalbedrag van de producten die de tekst 'T-Shirt' bevatten te verkrijgen; zowel de functie SOM.ALS als de functie SOMPRODUCT kunnen deze taak in Excel afhandelen.

• Waarden sommeren als cel specifieke of gedeeltelijke tekst bevat met SOM.ALS-functie
• Waarden sommeren als cel specifieke of gedeeltelijke tekst bevat met SOMPRODUCT-functie

Waarden sommeren als cel specifieke of gedeeltelijke tekst bevat met SOM.ALS-functie

Om waarden op te tellen als een cel specifieke tekst bevat in een andere kolom, kun je de SOM.ALS-functie gebruiken met een jokerteken (*), de algemene syntaxis is:

Algemene formule met hardcoded tekst:

• bereik: Het gegevensbereik dat je wilt evalueren met behulp van de criteria;
• *tekst*: De criteria waarop je waarden wilt optellen. Hier wordt het *-jokerteken gebruikt om elk aantal tekens te vinden, om alle items die een specifieke tekst bevatten te matchen, plaats de tekst tussen de twee *-tekens. (Let op: je moet de tekst en het jokerteken tussen dubbele aanhalingstekens plaatsen.)
• som_bereik: Het bereik van cellen met overeenkomende numerieke waarden die je wilt optellen.

Algemene formule met celverwijzing:

• bereik: Het gegevensbereik dat je wilt evalueren met behulp van de criteria;
• "*"&cel&"*": De criteria waarop je waarden wilt optellen;
  • *: Het jokerteken dat elk aantal tekens vindt.
  • cel: De cel bevat de specifieke tekst om naar te zoeken.
  • &: Deze concatenatie-operator (&) wordt gebruikt om een celverwijzing samen te voegen met de sterretjes.
• som_bereik: Het bereik van cellen met overeenkomende numerieke waarden die je wilt optellen.

Nadat je de basiswerking van de functie kent, gebruik dan een van de volgende formules die je nodig hebt, en druk vervolgens op de Enter-toets om het resultaat te krijgen:

Opmerking: Deze SOM.ALS-functie is niet hoofdlettergevoelig.

Waarden sommeren als cel specifieke of gedeeltelijke tekst bevat met SOMPRODUCT-functie

De SOMPRODUCT-functie kan je ook helpen om deze taak uit te voeren zonder enig jokerteken te gebruiken. Hier moet je de functies ISGETAL en ZOEKEN binnen een SOMPRODUCT-functie nesten, de algemene syntaxis is:

• som_bereik: Het bereik van cellen met overeenkomende numerieke waarden die je wilt optellen;
• criteria: De criteria waarop je waarden wilt optellen. Dit kan een celverwijzing of specifieke tekst zijn die je hebt gedefinieerd;
• bereik: Het gegevensbereik dat je wilt evalueren met behulp van de criteria;

Gebruik een van de volgende formules in een lege cel, en druk op de Enter-toets om het resultaat te retourneren:

Uitleg van deze formule:

=SOMPRODUCT($B$2:$B$12*(ISGETAL(ZOEKEN("T-Shirt",$A$2:$A$12))))

• ZOEKEN("T-Shirt",$A$2:$A$12): De functie ZOEKEN retourneert de locatie van de specifieke tekst 'T-Shirt' uit het gegevensbereik A2:A12, dus krijg je een array zoals deze: {5;#WAARDE!;#WAARDE!;7;#WAARDE!;7;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;7}.
• ISGETAL(ZOEKEN("T-Shirt",$A$2:$A$12))= ISGETAL({5;#WAARDE!;#WAARDE!;7;#WAARDE!;7;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;7}): Deze ISGETAL-functie wordt gebruikt om numerieke waarden te testen, en retourneert een nieuwe array: {WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;WAAR}.
• $B$2:$B$12*(ISGETAL(ZOEKEN("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;WAAR}: Om deze twee arrays te vermenigvuldigen, dwingt de wiskundige bewerking de WAAR en ONWAAR-waarden om naar 1-en en 0-en te worden omgezet. Dus zal de vermenigvuldiging van de twee arrays er als volgt uitzien: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SOMPRODUCT($B$2:$B$12*(ISGETAL(ZOEKEN("T-Shirt",$A$2:$A$12)))) =SOMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): Tenslotte sommeert de SOMPRODUCT-functie alle waarden in de array om het resultaat te krijgen: 1605.

Gerelateerde functies gebruikt:

Meer artikelen: