Генеричка формула
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Резиме
Да бисте бројали ћелије које садрже к или и, можете користити формулу засновану на функцији СУМПРОДУЦТ. У приказаном примеру, формула у ћелији Ф5 је:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Ово је решење једноструке формуле, објашњено у наставку. Такође је могуће користити једноставнију формулу засновану на помоћном ступцу, такође објашњеном у наставку.
Објашњење
Када бројате ћелије са „ИЛИ логиком“, морате бити опрезни да не будете двоструко бројени. На пример, ако бројате ћелије које садрже „абц“ или „деф“, не можете само да додате две функције ЦОУНТИФ, јер можете двоструко да пребројите ћелије које садрже и „абц“ и „деф“.
Једноћелијски раствор
За једну формулу можете да користите СУМПРОДУЦТ са ИСУМБЕР + ФИНД. Формула у Ф5 је:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ова формула се заснива на овде објашњеној формули која проналази текст унутар ћелије:
ISNUMBER(FIND("abc",B5:B11)
Када добије опсег ћелија, овај исечак ће вратити низ вредности ТРУЕ / ФАЛСЕ, по једну вредност за сваку ћелију опсега. Пошто ово користимо два пута (једном за "абц" и једном за "деф"), добићемо два низа.
Даље, те низове додајемо заједно (са +), што ствара нови појединачни низ бројева. Сваки број у овом низу резултат је збрајања вредности ТРУЕ и ФАЛСЕ у првобитна два низа. У приказаном примеру резултујући низ изгледа овако:
(2;0;2;0;1;0;2)
Морамо да збројимо ове бројеве, али не желимо да се двоструко броје. Зато морамо бити сигурни да се било која вредност већа од нуле броји само једном. Да бисмо то урадили, присиљавамо све вредности на ТРУЕ или ФАЛСЕ са "> 0", а затим на двоструко негативне (-) на 1/0.
Коначно, СУМПРОДУЦТ враћа збир свих вредности у низу.
Решење колоне помоћника
Са помоћном колоном за појединачну проверу сваке ћелије, проблем је мање сложен. Можемо да користимо ЦОУНТИФ са две вредности (предвиђене као „константа низа“). Формула у Ц5 је:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
ЦОУНТИФ ће вратити низ који садржи две ставке: број за "абц" и број за "деф". Да бисмо спречили двоструко бројање, збрајамо ставке, а затим форсирамо резултат на ТРУЕ / ФАЛСЕ са "> 0". На крају, вредности ТРУЕ / ФАЛСЕ претварамо у 1 и 0 са двоструким негативом (-).
Коначни резултат је 1 или 0 за сваку ћелију. Да бисте добили збир за све ћелије у опсегу, једноставно збројите помоћни ступац.
