Všeobecný vzorec
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Zhrnutie
Ak chcete zhrnúť, či bunky obsahujú buď jeden textový reťazec alebo iný (tj. Obsahujú „mačku“ alebo „potkana“), môžete použiť funkciu SUMPRODUCT spolu s ISNUMBER + SEARCH alebo FIND. V zobrazenom príklade je vzorec v bunke F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
ktorý vráti súčet hodnôt v C4: C8, keď bunky v B4: B8 obsahujú buď „mačku“ alebo „potkana“.
Vysvetlenie
Keď sčítate bunky s kritériami „ALEBO“, musíte byť opatrní, aby ste nezdvojnásobili počet, keď existuje možnosť, že obe kritériá vrátia pravdu. V zobrazenom príklade chceme sčítať hodnoty v stĺpci C, keď bunky v stĺpci B obsahujú slovo „mačka“ alebo „potkan“. SUMIF nemôžeme použiť s dvoma kritériami, pretože SUMIFS je založený na logike AND. A ak sa pokúsime použiť dva SUMIFY (tj. SUMIFS + SUMIFS), počet sa zdvojnásobí, pretože existujú bunky, ktoré obsahujú „mačku“ aj „potkana“.
Namiesto toho použijeme nasledujúci vzorec:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Toto jadro tohto vzorca je založené na tu vysvetlenom vzorci, ktorý lokalizuje text vo vnútri bunky s ISNUMBER a SEARCH:
ISNUMBER(SEARCH("text",range)
Keď dostane daný rozsah buniek, tento úryvok vráti pole hodnôt TRUE / FALSE, jednu hodnotu pre každú bunku rozsahu. V tomto vzorci použijeme tento úryvok dvakrát, raz pre „mačku“ a raz pre „potkana“, takže dostaneme dve polia. V tejto chvíli máme:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Ďalej pridáme tieto polia dohromady, pretože v logickej algebre sa pre logiku ALEBO používa sčítanie. Matematická operácia automaticky vynúti hodnoty TRUE a FALSE na 1 s a 0 s, takže skončíme s nižšie uvedeným poľom:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Každé číslo v tomto poli je výsledkom súčtu hodnôt TRUE a FALSE v pôvodných dvoch poliach. V zobrazenom príklade pole vyzerá takto:
(2;0;2;1;0)
Musíme tieto čísla spočítať, ale nechceme ich zdvojnásobiť. Musíme sa teda ubezpečiť, že akákoľvek hodnota vyššia ako nula sa počíta iba raz. Za týmto účelom vynútime všetky hodnoty na TRUE alebo FALSE zaškrtnutím poľa pomocou „> 0“. Týmto sa vráti PRAVDA / NEPRAVDA:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Ktoré potom prevedieme na 1/0 pomocou dvojitého záporného čísla (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
a nakoniec:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT znásobí zodpovedajúce prvky oboch polí a sčíta výsledok s návratom 70.
Možnosť rozlišovať veľké a malé písmená
Funkcia SEARCH ignoruje veľké a malé písmená. Ak potrebujete citlivú voľbu, nahraďte SEARCH funkciou FIND.
