Formula generică
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
rezumat
Pentru a rezuma dacă celulele conțin fie un șir de text, fie altul (adică conțin „pisică” sau „șobolan”) puteți utiliza funcția SUMPRODUCT împreună cu ISNUMBER + SEARCH sau FIND. În exemplul prezentat, formula din celula F5 este:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
care returnează suma valorilor din C4: C8 când celulele din B4: B8 conțin fie „pisică”, fie „șobolan”.
Explicaţie
Când sumați celulele cu criterii „SAU”, trebuie să aveți grijă să nu contorizați dublu când există posibilitatea ca ambele criterii să revină adevărate. În exemplul prezentat, dorim să însumăm valorile din coloana C atunci când celulele din coloana B conțin fie „pisică”, fie „șobolan”. Nu putem folosi SUMIF-uri cu două criterii, deoarece SUMIFS se bazează pe logica AND. Și dacă încercăm să folosim două SUMIFE (adică SUMIFE + SUMIFE) vom conta dublu deoarece există celule care conțin atât „pisică”, cât și „șobolan”.
În schimb, folosim o formulă ca aceasta:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Acest nucleu al acestei formule se bazează pe formula explicată aici care localizează textul în interiorul unei celule cu ISNUMBER și SEARCH:
ISNUMBER(SEARCH("text",range)
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. În această formulă, folosim acest fragment de două ori, o dată pentru „pisică” și o dată pentru „șobolan”, deci vom obține două matrice. În acest moment, avem:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Apoi, adăugăm aceste matrici împreună, deoarece adăugarea este utilizată în algebra booleană pentru logica SAU. Operația matematică constrânge automat valorile TRUE și FALSE la 1s și 0s, deci ajungem la matricea de mai jos:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Fiecare număr din această matrice este rezultatul adăugării valorilor TRUE și FALSE în cele două matrice originale. În exemplul prezentat, matricea arată astfel:
(2;0;2;1;0)
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 verificând matricea cu „> 0”. Aceasta returnează TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Pe care apoi le convertim la 1/0 folosind un dublu negativ (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
și, în sfârșit:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multiplică elementele corespunzătoare din cele două matrice împreună și însumează rezultatul, returnând 70.
Opțiune sensibilă la majuscule
Funcția SEARCH ignoră majuscule. Dacă aveți nevoie de o opțiune sensibilă, înlocuiți SEARCH cu funcția FIND.
