Általános képlet
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Összegzés
Összegezve, ha a cellák tartalmaznak egy vagy másik szövegláncot (azaz "macska" vagy "patkány" tartalmaznak), használhatja a SUMPRODUCT függvényt az ISNUMBER + SEARCH vagy FIND paranccsal együtt. A bemutatott példában az F5 cellában szereplő képlet a következő:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
amely a C4: C8 értékeinek összegét adja vissza, ha a B4: B8 sejtjei vagy "cat" vagy "patkány" -ot tartalmaznak.
Magyarázat
Amikor "OR" feltételekkel rendelkező cellákat összesít, akkor ügyelnie kell arra, hogy ne számolja meg kétszer, ha fennáll annak a lehetősége, hogy mindkét feltétel igaz lesz. A bemutatott példában a C oszlop értékeit szeretnénk összegezni, ha a B oszlop cellái vagy "cat" vagy "patkány" -ot tartalmaznak. Két kritériummal nem használhatjuk a SUMIF-okat, mert a SUMIFS az AND logikán alapul. És ha megpróbálunk két SUMIF-ot használni (azaz SUMIFS + SUMIFS), akkor duplán meg fogjuk számolni, mert vannak olyan sejtek, amelyek tartalmazzák a "macska" és a "patkány" kifejezéseket is.
Ehelyett egy ilyen képletet használunk:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ennek a képletnek a magja az itt ismertetett képleten alapul, amely a cellában lévő szöveget az ISNUMBER és a SEARCH kifejezéssel keresi:
ISNUMBER(SEARCH("text",range)
Amikor cellatartományt ad, ez a kódrészlet TRUE / FALSE értékek tömbjét adja vissza, egy értéket a tartomány minden cellájához. Ebben a képletben ezt a kódrészletet kétszer használjuk, egyszer a "macska", egyszer pedig a "patkány" kifejezésre, így két tömböt kapunk. Ezen a ponton:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Ezután összeadjuk ezeket a tömböket, mert az összeadást a logikai algebra az OR logikához használja. A matematikai művelet az IGAZ és HAMIS értékeket automatikusan 1 és 0 értékre kényszeríti, így végül az alábbi tömböt kapjuk:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Ebben a tömbben minden egyes szám az eredeti két tömb TRUE és FALSE értékeinek összeadásának eredménye. A bemutatott példában a tömb így néz ki:
(2;0;2;1;0)
Összeadnunk kell ezeket a számokat, de nem akarjuk megduplázni. Tehát meg kell győződnünk arról, hogy a nullánál nagyobb értékeket csak egyszer számolják-e. Ehhez az összes értéket IGAZ vagy HAMIS értékre kényszerítjük a tömb "> 0" jelölésével. Ez visszaadja az IGAZ / HAMIS értéket:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Amit aztán kettős negatív (-) segítségével 1/0-ra konvertálunk:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
és végül:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
A SUMPRODUCT megsokszorozza a két tömb megfelelő elemeit, és összegzi az eredményt, 70-t eredményezve.
Kis- és nagybetű érzékeny lehetőség
A KERESÉS funkció figyelmen kívül hagyja a kis- és nagybetűket. Ha kényes opcióra van szüksége, cserélje ki a KERESÉST a KERESÉS funkcióra.
