Генеричка формула
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Резиме
Да бисте сумирали да ли ћелије садрже или један или други текстуални низ (тј. Садрже „мачка“ или „пацов“), можете да користите функцију СУМПРОДУЦТ заједно са ИСУМБЕР + СЕАРЦХ или ФИНД. У приказаном примеру, формула у ћелији Ф5 је:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
који враћа збир вредности у Ц4: Ц8 када ћелије у Б4: Б8 садрже или „мачка“ или „пацов“.
Објашњење
Када сумирате ћелије са критеријумима „ИЛИ“, морате бити опрезни да не будете двоструко бројени када постоји могућност да се оба критеријума врате. У приказаном примеру желимо да сумирамо вредности у колони Ц када ћелије у колони Б садрже или „мачка“ или „пацов“. Не можемо да користимо СУМИФ са два критеријума, јер се СУМИФС заснива на логици АНД. А ако покушамо да користимо два СУМИФ-а (тј. СУМИФС + СУМИФС), двоструко ћемо бројати јер постоје ћелије које садрже и „мачка“ и „пацов“.
Уместо тога користимо формулу попут ове:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ово језгро ове формуле заснива се на овде објашњеној формули која проналази текст унутар ћелије са БРОЈИМА и ПРЕТРАЖИВАЊЕМ:
ISNUMBER(SEARCH("text",range)
Када добије опсег ћелија, овај исечак ће вратити низ вредности ТРУЕ / ФАЛСЕ, по једну вредност за сваку ћелију опсега. У овој формули користимо овај исечак два пута, једном за „мачка“ и једном за „пацов“, тако да ћемо добити два низа. У овом тренутку имамо:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Даље, ове низове додајемо заједно, јер се сабирање користи у логичкој алгебри за логику ОР. Математичка операција аутоматски присиљава вредности ТРУЕ и ФАЛСЕ на 1с и 0с, тако да ћемо добити доњи низ:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Сваки број у овом низу резултат је збрајања вредности ТРУЕ и ФАЛСЕ у првобитна два низа. У приказаном примеру низ изгледа овако:
(2;0;2;1;0)
Морамо да збројимо ове бројеве, али не желимо да се двоструко броје. Зато морамо бити сигурни да се било која вредност већа од нуле броји само једном. Да бисмо то урадили, присиљавамо све вредности на ТРУЕ или ФАЛСЕ проверавањем низа са „> 0“. Ово враћа ТРУЕ / ФАЛСЕ:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Који затим претварамо у 1/0 помоћу двоструког негатива (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
и коначно:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
СУМПРОДУЦТ множи одговарајуће елементе два низа и сумира резултат, враћајући 70.
Опција осетљива на велика и мала слова
Функција СЕАРЦХ занемарује велика и мала слова. Ако вам је потребна осетљива опција, замените СЕАРЦХ функцијом ФИНД.
