Загальна формула
=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 містять або "кішка", або "щур".
Пояснення
Коли ви підсумовуєте клітинки з критеріями "АБО", вам слід бути обережним, щоб не подвоїти рахунок, коли існує ймовірність того, що обидва критерії повернуть істину. У наведеному прикладі ми хочемо підсумувати значення у стовпці C, коли клітинки у стовпці B містять або "cat", або "rat". Ми не можемо використовувати SUMIF з двома критеріями, оскільки SUMIFS базується на логіці AND. І якщо ми спробуємо використати два SUMIFS (тобто SUMIFS + SUMIFS), ми подвоїмо рахунок, оскільки є клітини, які містять як "кішка", так і "щур".
Натомість ми використовуємо таку формулу:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Це ядро цієї формули базується на формулі, яка пояснюється тут, яка розміщує текст всередині комірки з ІСНОВИЛОЮ та ПОШУКОМ:
ISNUMBER(SEARCH("text",range)
Якщо йому задано діапазон комірок, цей фрагмент поверне масив значень TRUE / FALSE, по одному значенню для кожної комірки діапазону. У цій формулі ми використовуємо цей фрагмент двічі, один раз для "cat" і один раз для "rat", тож ми отримаємо два масиви. На даний момент ми маємо:
=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 ігнорує регістр. Якщо вам потрібна делікатна опція, замініть ПОШУК функцією ЗНАЙТИ.
