Generička formula
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Sažetak
Da biste brojali stanice koje sadrže x ili y, možete koristiti formulu koja se temelji na funkciji SUMPRODUCT. U prikazanom primjeru formula u ćeliji F5 je:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Ovo je rješenje za jednostruku stanicu, objašnjeno u nastavku. Također je moguće koristiti jednostavniju formulu koja se temelji na pomoćnom stupcu, također objašnjenom u nastavku.
Obrazloženje
Kada brojite stanice s "ILI logikom", morate paziti da se ne broji dvostruko. Na primjer, ako brojite stanice koje sadrže "abc" ili "def", ne možete samo zbrojiti dvije funkcije COUNTIF, jer možete dvostruko brojati stanice koje sadrže i "abc" i "def".
Otopina jednoćelije
Za jednu formulu možete upotrijebiti SUMPRODUCT s ISUMBER + FIND. Formula u F5 je:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ova se formula temelji na ovdje objašnjenoj formuli koja smješta tekst unutar ćelije:
ISNUMBER(FIND("abc",B5:B11)
Kada se dobije raspon ćelija, ovaj isječak vratit će niz TRUE / FALSE vrijednosti, po jednu vrijednost za svaku ćeliju raspona. Budući da ovo koristimo dva puta (jednom za "abc" i jednom za "def"), dobit ćemo dva polja.
Dalje, te nizove dodajemo zajedno (sa +), što stvara novi pojedinačni niz brojeva. Svaki broj u ovom nizu rezultat je zbrajanja vrijednosti TRUE i FALSE u izvorna dva polja. U prikazanom primjeru rezultirajući niz izgleda ovako:
(2;0;2;0;1;0;2)
Moramo zbrojiti ove brojeve, ali ne želimo dvostruko brojati. Dakle, moramo osigurati da se bilo koja vrijednost veća od nule samo jednom broji. Da bismo to učinili, prisiljavamo sve vrijednosti na TRUE ili FALSE s "> 0", a zatim s dvostrukim negativom (-) na 1/0.
Konačno, SUMPRODUCT vraća zbroj svih vrijednosti u polju.
Rješenje pomoćnog stupca
S pomoćnim stupcem za provjeru svake stanice pojedinačno, problem je manje složen. Možemo koristiti COUNTIF s dvije vrijednosti (pružaju se kao "konstanta niza"). Formula u C5 je:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF će vratiti niz koji sadrži dvije stavke: broj "abc" i broj "def". Da bismo spriječili dvostruko brojanje, zbrajamo stavke, a zatim prisiljavamo rezultat na TRUE / FALSE s "> 0". Na kraju, vrijednosti TRUE / FALSE pretvaramo u 1 i 0 s dvostrukim negativom (-).
Konačni rezultat je 1 ili 0 za svaku ćeliju. Da biste dobili zbroj za sve ćelije u rasponu, jednostavno zbrojite pomoćni stupac.
