Generieke formule
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Samenvatting
Om te tellen hoe vaak een specifiek woord (of een subtekenreeks) in een celbereik voorkomt, kunt u een formule gebruiken die is gebaseerd op de functies SUBSTITUTE, LEN en SUMPRODUCT. In het getoonde voorbeeld is de formule in C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Opmerking: de formule op deze pagina telt de substanties van een woord in een bereik. Als een cel bijvoorbeeld twee exemplaren van een woord bevat, draagt deze 2 bij aan het totale aantal. Als u alleen cellen wilt tellen die een specifiek woord bevatten, raadpleegt u deze eenvoudige formule op basis van de AANTAL.ALS-functie.
Uitleg
In de generieke versie van de formule staat rng voor het bereik dat moet worden gecontroleerd, en txt is het woord of de substring die moet worden geteld.
In het getoonde voorbeeld is B5: B8 het bereik dat moet worden gecontroleerd en bevat C2 de tekst (woord of substring) die moet worden geteld.
Voor elke cel in het bereik verwijdert SUBSTITUTE de subtekenreeks uit de originele tekst en berekent LEN de lengte van de tekst zonder de subtekenreeks. Dit aantal wordt vervolgens afgetrokken van de lengte van de originele tekst. Het resultaat is het aantal tekens dat is verwijderd door SUBSTITUTE.
Vervolgens wordt het aantal verwijderde tekens gedeeld door de lengte van de subtekenreeks. Dus als een substring of woord 5 tekens lang is en er ontbreken 10 tekens nadat het uit de originele tekst is verwijderd, weten we dat de substring / het woord twee keer in de originele tekst voorkomt.
Omdat de bovenstaande berekening is verpakt in de SOMPRODUCT-functie, is het resultaat een matrix die een getal bevat voor elke cel in het bereik. Deze cijfers vertegenwoordigen het aantal keren dat de subtekenreeks in elke cel voorkomt. Voor dit voorbeeld ziet de array er als volgt uit: (1; 1; 0; 1)
Ten slotte telt SOMPRODUCT alle items in de array bij elkaar op om het totale aantal gevallen van subtekenreeks in het celbereik te krijgen.
Case negeren
SUBSTITUTE is een hoofdlettergevoelige functie, dus het komt overeen met hoofdlettergebruik bij het uitvoeren van een vervanging. Als u zowel hoofdletters als kleine letters van een woord of substring moet tellen, gebruikt u de functie HOOFDLETTERS in SUBSTITUTE om de tekst naar hoofdletters te converteren voordat u de vervanging uitvoert:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
