Обща формула
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Обобщение
За да обобщите дали клетките съдържат или един или друг текстов низ (т.е. съдържат "котка" или "плъх"), можете да използвате функцията SUMPRODUCT заедно с ISNUMBER + SEARCH или FIND. В показания пример формулата в клетка F5 е:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
което връща сумата на стойностите в C4: C8, когато клетките в B4: B8 съдържат или „котка“, или „плъх“.
Обяснение
Когато сумирате клетки с критерии „ИЛИ“, трябва да внимавате да не отчитате двойно, когато има вероятност и двата критерия да върнат true. В показания пример искаме да сумираме стойности в колона C, когато клетките в колона B съдържат или "котка", или "плъх". Не можем да използваме SUMIF с два критерия, защото SUMIFS се основава на логиката И. И ако се опитаме да използваме две SUMIFS (т.е. SUMIFS + SUMIFS), ще удвоим броенето, защото има клетки, които съдържат едновременно "cat" и "rat".
Вместо това използваме формула като тази:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Това ядро на тази формула се основава на формулата, обяснена тук, която локализира текста вътре в клетка с ISNUMBER и SEARCH:
ISNUMBER(SEARCH("text",range)
Когато му бъде даден диапазон от клетки, този фрагмент ще върне масив от TRUE / FALSE стойности, по една стойност за всяка клетка в диапазона. В тази формула използваме този фрагмент два пъти, веднъж за "котка" и веднъж за "плъх", така че ще получим два масива. Към този момент имаме:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
След това добавяме тези масиви заедно, защото добавянето се използва в булева алгебра за ИЛИ логика. Операцията по математика автоматично принуждава стойностите TRUE и FALSE до 1s и 0s, така че завършваме с масива по-долу:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Всяко число в този масив е резултат от добавяне на стойностите TRUE и FALSE в оригиналните два масива заедно. В показания пример масивът изглежда така:
(2;0;2;1;0)
Трябва да съберем тези числа, но не искаме да броим двойно. Затова трябва да се уверим, че всяка стойност, по-голяма от нула, се брои само веднъж. За целта налагаме всички стойности на TRUE или FALSE, като проверяваме масива с "> 0". Това връща TRUE / FALSE:
=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))
SUMPRODUCT умножава съответните елементи от двата масива заедно и сумира резултата, връщайки 70.
Опция, чувствителна към малки и големи букви
Функцията SEARCH игнорира регистъра. Ако имате нужда от чувствителна опция, заменете SEARCH с функцията FIND.
