Formula generică
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
rezumat
Pentru a număra celulele care conțin fie x, fie y, puteți utiliza o formulă bazată pe funcția SUMPRODUCT. În exemplul prezentat, formula din celula F5 este:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Aceasta este soluția cu o singură celulă, explicată mai jos. De asemenea, este posibil să utilizați o formulă mai simplă bazată pe o coloană de ajutor, explicată și mai jos.
Explicaţie
Când numărați celule cu „OR logică”, trebuie să aveți grijă să nu contorizați dublu. De exemplu, dacă numărați celule care conțin „abc” sau „def”, nu puteți adăuga doar două funcții COUNTIF, deoarece puteți număra dublu celule care conțin atât „abc”, cât și „def”.
Soluție cu celulă unică
Pentru o singură formulă, puteți utiliza SUMPRODUCT cu ISNUMBER + FIND. Formula din F5 este:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Această formulă se bazează pe formula explicată aici care localizează textul în interiorul unei celule:
ISNUMBER(FIND("abc",B5:B11)
Atunci când este dat un interval de celule, acest fragment va returna o serie de valori TRUE / FALSE, o valoare pentru fiecare celulă din interval. Deoarece folosim acest lucru de două ori (o dată pentru „abc” și o dată pentru „def”), vom obține două matrice.
Apoi, adăugăm aceste matrice împreună (cu +), ceea ce creează o nouă matrice unică de numere. Fiecare număr din această matrice este rezultatul adăugării valorilor TRUE și FALSE în cele două matrice originale. În exemplul prezentat, matricea rezultată arată astfel:
(2;0;2;0;1;0;2)
Trebuie să adunăm aceste numere, dar nu vrem să numărăm dublu. Deci, trebuie să ne asigurăm că orice valoare mai mare decât zero este numărată o singură dată. Pentru a face acest lucru, forțăm toate valorile la TRUE sau FALSE cu "> 0", apoi forțăm la 1/0 cu dublu-negativ (-).
În cele din urmă, SUMPRODUCT returnează suma tuturor valorilor din matrice.
Soluție coloană de ajutor
Cu o coloană de ajutor pentru a verifica fiecare celulă individual, problema este mai puțin complexă. Putem folosi COUNTIF cu două valori (furnizate ca o „constantă de matrice”). Formula din C5 este:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF va returna o matrice care conține două elemente: un număr pentru „abc” și un număr pentru „def”. Pentru a preveni numărarea dublă, adăugăm elementele și apoi forțăm rezultatul la TRUE / FALSE cu „> 0”. În cele din urmă, convertim valorile TRUE / FALSE în 1 și 0 cu un dublu negativ (-).
Rezultatul final este fie 1, fie 0 pentru fiecare celulă. Pentru a obține un total pentru toate celulele din interval, pur și simplu sumați coloana de ajutor.
