Общая формула
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Резюме
Чтобы подсчитать ячейки, содержащие x или y, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула в ячейке F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Это решение формулы для одной ячейки, описанное ниже. Также можно использовать более простую формулу на основе вспомогательного столбца, что также объясняется ниже.
Объяснение
При подсчете ячеек с использованием логики «ИЛИ» нужно быть осторожным, чтобы не пересчитать дважды. Например, если вы подсчитываете ячейки, содержащие «abc» или «def», вы не можете просто сложить вместе две функции COUNTIF, потому что вы можете дважды подсчитать ячейки, содержащие как «abc», так и «def».
Одноклеточное решение
Для одной формулы вы можете использовать СУММПРОИЗВ с ISNUMBER + FIND. Формула в F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Эта формула основана на описанной здесь формуле, которая определяет местонахождение текста внутри ячейки:
ISNUMBER(FIND("abc",B5:B11)
Если задан диапазон ячеек, этот фрагмент вернет массив значений ИСТИНА / ЛОЖЬ, по одному значению для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и один раз для «def»), мы получим два массива.
Затем мы складываем эти массивы вместе (с +), что создает новый единый массив чисел. Каждое число в этом массиве является результатом сложения значений ИСТИНА и ЛОЖЬ в исходных двух массивах вместе. В показанном примере результирующий массив выглядит так:
(2;0;2;0;1;0;2)
Нам нужно сложить эти числа, но мы не хотим двойного счета. Поэтому нам нужно убедиться, что любое значение больше нуля учитывается только один раз. Для этого мы устанавливаем все значения в ИСТИНА или ЛОЖЬ с "> 0", затем устанавливаем 1/0 с двойным отрицательным знаком (-).
Наконец, СУММПРОИЗВ возвращает сумму всех значений в массиве.
Решение вспомогательной колонки
С помощью вспомогательного столбца для проверки каждой ячейки по отдельности проблема становится менее сложной. Мы можем использовать COUNTIF с двумя значениями (предоставленными как «константа массива»). Формула в C5:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF вернет массив, содержащий два элемента: счетчик для «abc» и счет для «def». Чтобы предотвратить двойной подсчет, мы складываем элементы, а затем устанавливаем для результата значение ИСТИНА / ЛОЖЬ с помощью «> 0». Наконец, мы преобразуем значения ИСТИНА / ЛОЖЬ в единицы и 0 с двойным отрицательным знаком (-).
Конечный результат - 1 или 0 для каждой ячейки. Чтобы получить сумму для всех ячеек в диапазоне, просто просуммируйте вспомогательный столбец.
