Všeobecný vzorec
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Zhrnutie
Ak chcete spočítať bunky, ktoré obsahujú buď x alebo y, môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v bunke F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Toto je jednobunkové riešenie vzorca, vysvetlené nižšie. Je tiež možné použiť jednoduchší vzorec založený na pomocnom stĺpci, ktorý je tiež vysvetlený nižšie.
Vysvetlenie
Pri počítaní buniek s logikou „ALEBO logika“ musíte byť opatrní, aby ste počet nezdvojnásobili. Napríklad, ak počítate bunky, ktoré obsahujú „abc“ alebo „def“, nemôžete pridať iba dve funkcie COUNTIF, pretože môžete počet buniek, ktoré obsahujú „abc“ aj „def“, zdvojnásobiť.
Jednobunkový roztok
Pre jeden vzorec môžete použiť SUMPRODUCT s ISNUMBER + FIND. Vzorec v F5 je:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Tento vzorec je založený na tu vysvetlenom vzorci, ktorý umiestňuje text do bunky:
ISNUMBER(FIND("abc",B5:B11)
Keď dostane daný rozsah buniek, tento úryvok vráti pole hodnôt TRUE / FALSE, jednu hodnotu pre každú bunku rozsahu. Pretože toto používame dvakrát (raz pre „abc“ a raz pre „def“), dostaneme dve polia.
Ďalej pridáme tieto polia dohromady (s +), čím sa vytvorí nové jedno pole čísel. 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 vyzerá výsledné pole takto:
(2;0;2;0;1;0;2)
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. Aby sme to dosiahli, vynútime všetky hodnoty na TRUE alebo FALSE s „> 0“, potom vynútime na 1/0 s dvojitým záporom (-).
Nakoniec program SUMPRODUCT vráti súčet všetkých hodnôt v poli.
Riešenie pomocnej kolóny
Vďaka pomocnému stĺpcu na kontrolu každej bunky jednotlivo je problém menej zložitý. Môžeme použiť COUNTIF s dvoma hodnotami (poskytované ako „konštanta poľa“). Vzorec v C5 je:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF vráti pole, ktoré obsahuje dve položky: počet pre „abc“ a počet pre „def“. Aby sme zabránili dvojitému započítaniu, sčítame položky a potom výsledok vynútime na „PRAVDA / NEPRAVDA“ pomocou „> 0“. Nakoniec prevedieme hodnoty TRUE / FALSE na 1 a 0 s dvojitým záporom (-).
Konečný výsledok je buď 1 alebo 0 pre každú bunku. Ak chcete získať súčet za všetky bunky v rozsahu, jednoducho sčítajte pomocný stĺpec.
