Generieke formule
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Samenvatting
Om cellen te tellen die x of y bevatten, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in cel F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Dit is de eencellige formule-oplossing, die hieronder wordt uitgelegd. Het is ook mogelijk om een eenvoudigere formule te gebruiken op basis van een hulpkolom, die hieronder ook wordt uitgelegd.
Uitleg
Wanneer u cellen telt met "OF-logica", moet u oppassen dat u niet dubbel telt. Als u bijvoorbeeld cellen telt die "abc" of "def" bevatten, kunt u niet zomaar twee AANTAL.ALS-functies bij elkaar optellen, omdat u cellen die zowel "abc" als "def" bevatten, dubbel kunt tellen.
Eencellige oplossing
Voor een enkele formule kunt u SOMPRODUCT gebruiken met ISNUMBER + FIND. De formule in F5 is:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Deze formule is gebaseerd op de formule die hier wordt uitgelegd en die tekst in een cel lokaliseert:
ISNUMBER(FIND("abc",B5:B11)
Wanneer een celbereik wordt gegeven, retourneert dit fragment een matrix met WAAR / ONWAAR-waarden, één waarde voor elke cel het bereik. Aangezien we dit twee keer gebruiken (één keer voor "abc" en één keer voor "def"), krijgen we twee arrays.
Vervolgens voegen we deze arrays samen (met +), waardoor een nieuwe enkele array met getallen ontstaat. Elk getal in deze array is het resultaat van het bij elkaar optellen van de waarden TRUE en FALSE in de oorspronkelijke twee arrays. In het getoonde voorbeeld ziet de resulterende array er als volgt uit:
(2;0;2;0;1;0;2)
We moeten deze getallen optellen, maar we willen niet dubbel tellen. We moeten er dus voor zorgen dat elke waarde groter dan nul maar één keer wordt geteld. Om dat te doen, forceren we alle waarden naar WAAR of ONWAAR met "> 0" en vervolgens forceren we naar 1/0 met de dubbele negatieve (-).
Ten slotte retourneert SOMPRODUCT de som van alle waarden in de array.
Hulpkolomoplossing
Met een hulpkolom om elke cel afzonderlijk te controleren, is het probleem minder complex. We kunnen AANTAL.ALS gebruiken met twee waarden (opgegeven als een "matrixconstante"). De formule in C5 is:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
AANTAL.ALS retourneert een array die twee items bevat: een telling voor "abc" en een telling voor "def". Om dubbeltellingen te voorkomen, tellen we de items op en forceren we het resultaat naar WAAR / ONWAAR met "> 0". Ten slotte converteren we de WAAR / ONWAAR-waarden naar enen en nullen met een dubbele negatieve (-).
Het uiteindelijke resultaat is 1 of 0 voor elke cel. Om een totaal te krijgen voor alle cellen in het bereik, tel je simpelweg de hulpkolom op.
