Generička formula
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Sažetak
Da zbrojite sadrže li stanice jedan ili drugi tekstualni niz (tj. Sadrže "mačka" ili "štakor"), možete upotrijebiti funkciju SUMPRODUCT zajedno s ISUMBER + SEARCH ili FIND. U prikazanom primjeru formula u ćeliji F5 je:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
koji vraća zbroj vrijednosti u C4: C8 kada stanice u B4: B8 sadrže ili "mačka" ili "štakor".
Obrazloženje
Kada zbrajate ćelije s "ILI" kriterijima, morate paziti da se dvostruko ne broji kada postoji mogućnost da se oba kriterija vrate. U prikazanom primjeru želimo zbrojiti vrijednosti u stupac C kada stanice u stupcu B sadrže ili "mačka" ili "štakor". SUMIF-ove ne možemo koristiti s dva kriterija, jer se SUMIFS temelji na logici AND. A ako pokušamo upotrijebiti dva SUMIFA (tj. SUMIFS + SUMIFS), dvostruko ćemo brojati jer postoje stanice koje sadrže i "mačku" i "štakor".
Umjesto toga koristimo formulu poput ove:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ova jezgra ove formule temelji se na ovdje objašnjenoj formuli koja smješta tekst unutar ćelije s BROJIMA i TRAŽENJEM:
ISNUMBER(SEARCH("text",range)
Kada se dobije raspon ćelija, ovaj isječak vratit će niz TRUE / FALSE vrijednosti, po jednu vrijednost za svaku ćeliju raspona. U ovoj formuli ovaj isječak koristimo dva puta, jednom za "mačka" i jednom za "štakor", tako da ćemo dobiti dva polja. U ovom trenutku imamo:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Dalje, zbrajamo te nizove jer se dodavanje koristi u logičkoj algebri za logiku OR. Matematička operacija automatski prisiljava vrijednosti TRUE i FALSE na 1s i 0s, pa ćemo završiti s donjim nizom:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Svaki broj u ovom nizu rezultat je zbrajanja vrijednosti TRUE i FALSE u izvorna dva polja. U prikazanom primjeru niz izgleda ovako:
(2;0;2;1;0)
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 provjerom niza s "> 0". Ovo vraća TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Koji zatim pretvaramo u 1/0 pomoću dvostrukog negativa (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
i konačno:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT množi odgovarajuće elemente dva polja i zbraja rezultat, vraćajući 70.
Opcija osjetljiva na velika i mala slova
Funkcija SEARCH zanemaruje velika i mala slova. Ako trebate osjetljivu opciju, zamijenite SEARCH s funkcijom FIND.
