Общая формула
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Резюме
Подводя итог, если ячейки содержат одну или другую текстовую строку (т.е. содержат «кот» или «крыса»), вы можете использовать функцию СУММПРОИЗВ вместе с ISNUMBER + SEARCH или FIND. В показанном примере формула в ячейке F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
который возвращает сумму значений в C4: C8, когда ячейки в B4: B8 содержат либо «кошка», либо «крыса».
Объяснение
При суммировании ячеек с помощью критерия «ИЛИ» необходимо соблюдать осторожность, чтобы не удвоить счет, если существует вероятность того, что оба критерия вернут истину. В показанном примере мы хотим суммировать значения в столбце C, когда ячейки в столбце B содержат либо «кот», либо «крыса». Мы не можем использовать СУММЕСЛИМН с двумя критериями, потому что СУММЕСЛИМН основано на логике И. И если мы попытаемся использовать два СУММЕСЛИМН (то есть СУММЕСЛИМН + СУММЕСЛИМН), мы получим двойной счет, потому что есть ячейки, которые содержат и «кот», и «крыса».
Вместо этого мы используем такую формулу:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Это ядро этой формулы основано на описанной здесь формуле, которая определяет местонахождение текста внутри ячейки с помощью ISNUMBER и SEARCH:
ISNUMBER(SEARCH("text",range)
Если задан диапазон ячеек, этот фрагмент вернет массив значений ИСТИНА / ЛОЖЬ, по одному значению для каждой ячейки диапазона. В этой формуле мы используем этот фрагмент дважды, один раз для слова «кошка» и один раз для слова «крыса», поэтому мы получим два массива. На данный момент у нас есть:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Затем мы складываем эти массивы вместе, потому что сложение используется в булевой алгебре для логики ИЛИ. Математическая операция автоматически переводит значения ИСТИНА и ЛОЖЬ в единицы и нули, так что мы получаем следующий массив:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Каждое число в этом массиве является результатом сложения значений ИСТИНА и ЛОЖЬ в исходных двух массивах вместе. В показанном примере массив выглядит так:
(2;0;2;1;0)
Нам нужно сложить эти числа, но мы не хотим двойного счета. Поэтому нам нужно убедиться, что любое значение больше нуля учитывается только один раз. Для этого мы переводим все значения в ИСТИНА или ЛОЖЬ, проверяя массив с «> 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.
Вариант с учетом регистра
Функция ПОИСК игнорирует регистр. Если вам нужна чувствительная опция, замените ПОИСК функцией НАЙТИ.
