СЧЁТЕСЛИМН с логикой ИЛИ для нескольких критериев в Excel

Обычно функция СЧЁТЕСЛИМН в Excel применяется для подсчёта ячеек по одному или нескольким условиям с логикой «И». А сталкивались ли вы с ситуацией, когда нужно подсчитать несколько разных значений из одного столбца или диапазона? Это уже подсчёт по нескольким условиям с логикой «ИЛИ». В таком случае можно комбинировать функции СУММ и СЧЁТЕСЛИМН или воспользоваться функцией СУММПРОИЗВ.

• Подсчёт ячеек с условиями ИЛИ в Excel
  • Подсчёт ячеек с условиями ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
  • Подсчёт ячеек с условиями ИЛИ с помощью функции СУММПРОИЗВ
• Подсчёт ячеек с несколькими группами условий ИЛИ в Excel
  • Подсчёт ячеек с двумя группами условий ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
  • Подсчёт ячеек с несколькими группами условий ИЛИ с помощью функции СУММПРОИЗВ

Подсчёт ячеек с условиями ИЛИ в Excel

Например, у меня есть диапазон данных, как показано на скриншоте ниже. Сейчас я хочу подсчитать количество товаров, которые являются «Карандашом» или «Линейкой». Далее я покажу две формулы, с помощью которых можно решить эту задачу в Excel.

Подсчёт ячеек с условиями ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН

В Excel для подсчёта с несколькими условиями ИЛИ можно использовать функции СУММ и СЧЁТЕСЛИМН в сочетании с константой массива. Общий синтаксис формулы:

• range: Диапазон Диапазон данных содержит критерии, по которым вы подсчитываете ячейки;
• criterion1, criterion2, criterion3…: Условия, по которым вы хотите подсчитать ячейки.

Чтобы подсчитать количество товаров, которые являются «Карандашом» или «Линейкой», скопируйте или введите приведённую ниже формулу в пустую ячейку, а затем нажмите клавишу Enter, чтобы получить результат:

Пояснение формулы:

=SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))

• {«Карандаш»,«Линейка»}: Сначала поместите все условия в константу массива следующим образом: {«Карандаш»,«Линейка»}, разделяя элементы запятыми.
• COUNTIFS(B2:B13,{«Pencil»,«Ruler»}): Эта функция СЧЁТЕСЛИМН выполнит отдельный подсчёт для «Карандаша» и «Линейки», и вы получите результат в виде: {2;3}.
• SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))=SUM({2,3}): Наконец, функция СУММ складывает все элементы массива и возвращает результат — 5.

Совет: вы также можете использовать ссылки на ячейки в качестве критериев. Введите приведённую ниже формулу массива и нажмите одновременно клавиши Ctrl + Shift + Enter, чтобы получить правильный результат:

Подсчёт ячеек с условиями ИЛИ с помощью функции СУММПРОИЗВ

Вот ещё одна формула на основе функции СУММПРОИЗВ, которая также позволяет подсчитывать ячейки по логике ИЛИ. Общий синтаксис:

• range: Диапазон Диапазон данных содержит критерии, по которым вы подсчитываете ячейки;
• criterion1, criterion2, criterion3…: Условия, по которым вы хотите подсчитывать ячейки.

Скопируйте или введите следующую формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить результат:

Пояснение формулы:

=SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))

• B2:B13={«Карандаш»,«Линейка»}: Это выражение сравнивает каждый элемент диапазона B2:B13 с критериями «Карандаш» и «Линейка». Если совпадение найдено, возвращается ИСТИНА, в противном случае — ЛОЖЬ. Результат будет выглядеть так: {ИСТИНА;ЛОЖЬ|ЛОЖЬ;ЛОЖЬ|ЛОЖЬ;ЛОЖЬ|ЛОЖЬ;ИСТИНА|ЛОЖЬ;ЛОЖЬ|ИСТИНА;ЛОЖЬ|ЛОЖЬ;ЛОЖЬ|ЛОЖЬ;ИСТИНА|ЛОЖЬ;ЛОЖЬ|ЛОЖЬ;ЛОЖЬ|ЛОЖЬ;ИСТИНА|ЛОЖЬ;ЛОЖЬ}.
• 1*(B2:B13={«Карандаш»,«Линейка»}): Умножение преобразует логические значения ИСТИНА и ЛОЖЬ в 1 и 0, поэтому результат будет следующим: {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): В завершение функция СУММПРОИЗВ суммирует все числа в массиве и возвращает результат — 5.

Подсчёт ячеек с несколькими группами условий ИЛИ в Excel

Иногда возникает необходимость подсчитать ячейки, удовлетворяющие двум или более группам условий ИЛИ. В таких случаях можно воспользоваться либо комбинацией функций СУММ и СЧЁТЕСЛИМН с константой массива, либо функцией СУММПРОИЗВ в паре с ЕЧИСЛО и ПОИСКПОЗ.

Подсчёт ячеек с двумя группами условий ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН

Чтобы работать всего с двумя группами условий ИЛИ, достаточно добавить вторую константу массива в формулу СЧЁТЕСЛИМН.

Например, у меня есть диапазон данных, как показано на скриншоте ниже. Сейчас я хочу подсчитать количество людей, которые заказали «Карандаш» или «Линейку» и у которых сумма заказа составляет 200.

Введите или скопируйте приведённую ниже формулу в ячейку и нажмите клавишу Enter, чтобы получить результат (см. скриншот):

: Во второй константе массива в формуле используйте точку с запятой — это создаёт вертикальный массив.

Подсчёт ячеек с несколькими группами условий ИЛИ с помощью функции СУММПРОИЗВ

Приведённая выше формула работает только для двух групп критериев ИЛИ. Чтобы учитывать большее количество критериев, используйте сложную формулу СУММПРОИЗВ в сочетании с функциями ЕЧИСЛО и ПОИСКПОЗ.

Возьмём для примера приведённые ниже данные. Чтобы подсчитать количество людей, заказавших «Карандаш» или «Линейку», у которых статус заказа — «Доставлен» или «В пути», а подпись — «Боб» или «Эко», потребуется использовать сложную формулу.

Скопируйте или вставьте приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить результат вычисления (см. скриншот):

Пояснение формулы:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0)))

ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0)):

• MATCH(B2:B13,{«Pencil»,«Ruler»},0): Функция ПОИСКПОЗ сравнивает каждую ячейку в диапазоне B2:B13 с элементами константного массива. Если совпадение найдено, возвращается относительная позиция значения в этом массиве; в противном случае — ошибка #Н/Д. В результате вы получите массив следующего вида: {1;#Н/Д;#Н/Д;2;#Н/Д;1;#Н/Д;2;1;#Н/Д;2;#Н/Д}.
• ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функция ЕЧИСЛО преобразует числовые значения в ИСТИНА, а ошибки — в ЛОЖЬ, формируя следующий массив: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.

Описанная выше логика также применима ко второму и третьему выражениям с функцией ЕЧИСЛО.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0))):

• Затем эти три массива перемножаются внутри функции СУММПРОИЗВ, которая автоматически преобразует логические значения ИСТИНА и ЛОЖЬ в 1 и 0 при выполнении математических операций следующим образом: СУММПРОИЗВ({1;0;0;1;0;1;0;1;1;0;1;0} * {1;1;0;0;1;1;0;1;1;0;1;1} * {1;0;0;0;0;0;0;1;0;0;1;0}) = СУММПРОИЗВ({1;0;0;0;0;0;0;1;0;0;1;0}).
• В завершение функция СУММПРОИЗВ суммирует все числа в массиве и возвращает результат: 3.

Используемые связанные функции:

Другие статьи: