Generieke formule
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Samenvatting
Om te sommeren als cellen een tekstreeks of een andere bevatten (dwz "kat" of "rat" bevatten), kunt u de SOMPRODUCT-functie samen met ISNUMBER + SEARCH of FIND gebruiken. In het getoonde voorbeeld is de formule in cel F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
die de som van de waarden in C4: C8 retourneert wanneer cellen in B4: B8 "kat" of "rat" bevatten.
Uitleg
Wanneer u cellen optelt met "OF" -criteria, moet u oppassen dat u niet dubbeltelt als de mogelijkheid bestaat dat beide criteria als true retourneren. In het getoonde voorbeeld willen we de waarden in kolom C optellen wanneer cellen in kolom B "kat" of "rat" bevatten. We kunnen geen SUMIF's gebruiken met twee criteria, omdat SUMIFS is gebaseerd op AND-logica. En als we twee SUMIFS (dwz SUMIFS + SUMIFS) proberen te gebruiken, zullen we dubbel tellen omdat er cellen zijn die zowel "kat" als "rat" bevatten.
In plaats daarvan gebruiken we een formule als deze:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Deze kern van deze formule is gebaseerd op de formule die hier wordt uitgelegd en die tekst in een cel lokaliseert met ISNUMBER en SEARCH:
ISNUMBER(SEARCH("text",range)
Wanneer een celbereik wordt gegeven, retourneert dit fragment een matrix met WAAR / ONWAAR-waarden, één waarde voor elke cel het bereik. In deze formule gebruiken we dit fragment twee keer, één keer voor "kat" en één keer voor "rat", dus we krijgen twee arrays. Op dit punt hebben we:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Vervolgens voegen we deze arrays bij elkaar omdat optellen wordt gebruikt in booleaanse algebra voor OR-logica. De wiskundige bewerking dwingt de waarden TRUE en FALSE automatisch naar 1s en 0s, dus we eindigen met de onderstaande array:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
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 array er als volgt uit:
(2;0;2;1;0)
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, dwingen we alle waarden naar TRUE of FALSE door de array te controleren met "> 0". Dit geeft WAAR / ONWAAR terug:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Die we vervolgens converteren naar 1/0 met een dubbel negatief (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
en tenslotte:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT vermenigvuldigt overeenkomstige elementen van de twee arrays samen en telt het resultaat op, resulterend in 70.
Hoofdlettergevoelige optie
De SEARCH-functie negeert hoofdletters / kleine letters. Als je een gevoelige optie nodig hebt, vervang dan SEARCH door de FIND-functie.
