Ga naar hoofdinhoud

Som als cellen specifieke tekst in een andere kolom bevatten

Auteur: Xiaoyang Laatst gewijzigd: 2021-11-19

Deze zelfstudie laat u zien hoe u waarden optelt als cellen specifieke of gedeeltelijke tekst in een andere kolom bevatten. Neem het onderstaande gegevensbereik als voorbeeld, om de totale hoeveelheden van de producten met de tekst "T-Shirt" te krijgen, kunnen zowel de SUMIF-functie als de SOMPRODUCT-functie deze taak in Excel uitvoeren.


Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SUMIF-functie

Als u waarden wilt optellen als een cel specifieke tekst in een andere kolom bevat, kunt u de SOM.ALS-functie gebruiken met een jokerteken (*). De generieke syntaxis zijn:

Generieke formule met hardgecodeerde tekst:

=SUMIF(range,"*text*",sum_range)
  • range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;
  • *text*: De criteria waarop u waarden wilt optellen op basis van. Hier wordt het * jokerteken gebruikt om een ​​willekeurig aantal tekens te vinden, om alle items die een specifieke tekst bevatten te matchen, plaats de tekst tussen de twee * tekens. (Merk op dat u de tekst en het jokerteken tussen dubbele aanhalingstekens moet plaatsen.)
  • sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen.

Generieke formule met celverwijzing:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;
  • "*"&cell&"*": de criteria waarop u waarden wilt optellen;
    • *: Het jokerteken waarmee een willekeurig aantal tekens wordt gevonden.
    • cel: de cel bevat de specifieke tekst waarnaar moet worden gezocht.
    • &: Deze aaneenschakelingsoperator (&) wordt gebruikt om een ​​celverwijzing samen te voegen met de sterretjes.
  • sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen.

Nadat u de basiswerking van de functie kent, gebruikt u een van de volgende formules die u nodig heeft en drukt u vervolgens op Enter sleutel om het resultaat te krijgen:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                 
 (Use a cell reference)

Note: Deze SUMIF-functie is niet hoofdlettergevoelig.


Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SOMPRODUCT-functie

De SOMPRODUCT-functie kan u ook helpen om deze taak te voltooien zonder een jokerteken te gebruiken. Hier moet u de ISNUMBER- en SEARCH-functies in een SOMPRODUCT-functie plaatsen, de generieke syntaxis is:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen;
  • criteria: De criteria waarop u waarden wilt optellen op basis van. Het kan een celverwijzing zijn of specifieke tekst die u hebt gedefinieerd;
  • range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;

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

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                   
(Use a cell reference)


Verklaring van deze formule:

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

  • ZOEKEN("T-Shirt",$A$2:$A$12): De SEARCH-functie retourneert de locatie van de specifieke tekst "T-Shirt" uit het gegevensbereik A2:A12, dus u krijgt een array als deze: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Deze functie ISNUMBER wordt gebruikt om te testen op de numerieke waarden en retourneert een nieuwe array: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Om deze twee arrays te vermenigvuldigen, dwingt de wiskundige bewerking de TRUE en FALSE-waarden tot 1s en 0s. De vermenigvuldiging van de twee arrays is dus als volgt: {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}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Ten slotte telt de functie SOMPRODUCT alle waarden in de array op om het resultaat:1605 te krijgen.

Gebruikte relatieve functie:

  • SUMIF:
  • De SUMIF-functie kan helpen om cellen op te tellen op basis van één criterium.
  • SUMPRODUCT:
  • De SOMPRODUCT-functie kan worden gebruikt om twee of meer kolommen of arrays met elkaar te vermenigvuldigen en vervolgens de som van producten te krijgen.
  • ISNUMMER:
  • De Excel ISNUMBER-functie retourneert WAAR wanneer een cel een getal bevat, en ONWAAR als dat niet het geval is.
  • ZOEKEN:
  • De SEARCH-functie kan u helpen om de positie van een specifiek teken of substring uit de opgegeven tekstreeks te vinden

Meer artikelen:

  • Som als begint met of eindigt met specifieke tekst of tekens
  • Als u waarden wilt optellen als corresponderende cellen beginnen met of eindigen met een specifieke waarde, kunt u de SUMIF-functie toepassen met een jokerteken (*) om deze functie uit te voeren. In dit artikel wordt uitgelegd hoe u de formule in detail kunt gebruiken.
  • Som kleinste of laagste N-waarden
  • In Excel kunnen we gemakkelijk een celbereik optellen met behulp van de SOM-functie. Soms moet u mogelijk de kleinste of onderste 3, 5 of n getallen in een gegevensbereik optellen, zoals hieronder wordt weergegeven. In dit geval kan de SOMPRODUCT samen met de SMALL-functie u helpen dit probleem in Excel op te lossen.
  • Som kleinste of laagste N-waarden op basis van criteria
  • In de vorige zelfstudie hebben we besproken hoe u de kleinste n-waarden in een gegevensbereik kunt optellen. In dit artikel zullen we een verdere geavanceerde bewerking uitvoeren - om de laagste n-waarden op te tellen op basis van een of meer criteria in Excel.

De beste tools voor kantoorproductiviteit

Kutools for Excel - Helpt u zich te onderscheiden van de menigte

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...

Omschrijving


Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)

  • Een seconde om te schakelen tussen tientallen geopende documenten!
  • Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
  • Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
  • Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations