Splošna formula
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Povzetek
Če želite šteti celice, ki vsebujejo x ali y, lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v celici F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
To je rešitev enocelične formule, razložena spodaj. Možna je tudi uporaba enostavnejše formule, ki temelji na pomožnem stolpcu, prav tako razloženo spodaj.
Pojasnilo
Ko štejete celice z "ALI logiko", morate biti previdni, da ne štejete dvojno. Če na primer štejete celice, ki vsebujejo "abc" ali "def", ne morete samo dodati dveh funkcij COUNTIF, ker lahko dvojno štejete celice, ki vsebujejo "abc" in "def".
Enocelična raztopina
Za eno samo formulo lahko uporabite SUMPRODUCT z ISNUMBER + FIND. Formula v F5 je:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ta formula temelji na tukaj razloženi formuli, ki poišče besedilo znotraj celice:
ISNUMBER(FIND("abc",B5:B11)
Ko dobi odrezek celic, bo ta delček vrnil niz TRUE / FALSE vrednosti, po eno vrednost za vsako celico obsega. Ker to uporabljamo dvakrat (enkrat za "abc" in enkrat za "def"), bomo dobili dva polja.
Nato te nize dodamo skupaj (z +), kar ustvari novo enojno polje števil. Vsako število v tej matriki je rezultat seštevanja vrednosti TRUE in FALSE v prvotnih dveh nizih. V prikazanem primeru je dobljena matrika videti tako:
(2;0;2;0;1;0;2)
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 z "> 0", nato pa na dvojno negativno (-) na 1/0.
Končno, SUMPRODUCT vrne vsoto vseh vrednosti v matriki.
Raztopina pomožne kolone
S pomožnim stolpcem za preverjanje vsake celice posebej je težava manj zapletena. Lahko uporabimo COUNTIF z dvema vrednostma (na voljo kot "konstanta matrike"). Formula v C5 je:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF bo vrnil matriko, ki vsebuje dva elementa: števec za "abc" in števec za "def". Da preprečimo dvojno štetje, seštevamo elemente in rezultat s silo "> 0" izsilimo na TRUE / FALSE. Končno pretvorimo vrednosti TRUE / FALSE v 1 in 0 z dvojnim negativom (-).
Končni rezultat je 1 ali 0 za vsako celico. Če želite dobiti seštevek za vse celice v območju, preprosto seštejte pomožni stolpec.
