Splošna formula
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Povzetek
Če želite povzeti, ali celice vsebujejo bodisi en besedilni niz bodisi drugega (tj. Vsebujejo "mačka" ali "podgana"), lahko uporabite funkcijo SUMPRODUCT skupaj z ISNUMBER + SEARCH ali FIND. V prikazanem primeru je formula v celici F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
ki vrne vsoto vrednosti v C4: C8, če celice v B4: B8 vsebujejo bodisi "mačka" ali "podgana".
Pojasnilo
Ko seštevate celice z merili "ALI", bodite previdni, da ne boste dvakrat šteli, če obstaja možnost, da oba merila vrneta true. V prikazanem primeru želimo v stolpec C sešteti vrednosti, če celice v stolpcu B vsebujejo bodisi "mačka" bodisi "podgana". SUMIF-ov ne moremo uporabljati z dvema kriterijema, ker SUMIFS temelji na logiki AND. In če poskusimo uporabiti dva SUMIFA (tj. SUMIFS + SUMIFS), bomo dvojno šteli, ker obstajajo celice, ki vsebujejo tako "mačka" kot "podgana".
Namesto tega uporabimo takšno formulo:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
To jedro te formule temelji na tukaj razloženi formuli, ki poišče besedilo znotraj celice s ŠTEVILO in ISKANJEM:
ISNUMBER(SEARCH("text",range)
Ko dobi odrezek celic, bo ta delček vrnil niz TRUE / FALSE vrednosti, po eno vrednost za vsako celico obsega. V tej formuli ta odlomek uporabimo dvakrat, enkrat za "mačka" in enkrat za "podgana", tako da bomo dobili dva polja. Na tej točki imamo:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Nato te nize seštejemo skupaj, ker se dodajanje uporablja v logični algebri za logiko OR. Matematična operacija samodejno prisili vrednosti TRUE in FALSE na 1s in 0s, zato dobimo spodnjo matriko:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Vsako število v tej matriki je rezultat seštevanja vrednosti TRUE in FALSE v prvotnih dveh nizih. V prikazanem primeru je matrika videti tako:
(2;0;2;1;0)
Te številke moramo sešteti, vendar ne želimo dvojno šteti. Zato moramo zagotoviti, da se katera koli vrednost, večja od nič, šteje samo enkrat. Če želite to narediti, prisilimo vse vrednosti na TRUE ali FALSE s preverjanjem polja z "> 0". To vrne TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Kar nato pretvorimo v 1/0 z dvojnim negativom (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
in končno:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT pomnoži ustrezne elemente obeh nizov in sešteje rezultat in vrne 70.
Možnost, ki razlikuje med velikimi in malimi črkami
Funkcija SEARCH ne upošteva velikih in malih črk. Če potrebujete občutljivo možnost, zamenjajte SEARCH s funkcijo FIND.
