Суммирование с несколькими критериями на основе логики ИЛИ и И

При работе с таблицами Excel вам может потребоваться суммировать значения на основе нескольких критериев. Иногда несколько критериев берутся из одного столбца (логика ИЛИ), а иногда из разных столбцов (логика И). В этом случае, как можно решить эту задачу в Excel?

• Суммирование с несколькими критериями на основе логики ИЛИ
  • Используя формулу СУММЕСЛИ + СУММЕСЛИ +…
  • Используя функции СУММ и СУММЕСЛИ
• Суммирование с несколькими критериями на основе логики И с использованием функции СУММЕСЛИМН

Суммирование с несколькими критериями на основе логики ИЛИ

Предположим, у вас есть список продуктов, который содержит поставщиков и количество, как показано на скриншоте ниже. Теперь вы хотите получить общее количество товаров, поставленных поставщиками Джоном и Дэвидом. Здесь я представлю вам две простые формулы.

Используя формулу СУММЕСЛИ + СУММЕСЛИ +…

Если вы хотите суммировать числа, соответствующие одному из нескольких критериев (логика ИЛИ), вы можете сложить несколько функций СУММЕСЛИ в одной формуле. Общий синтаксис следующий:

• диапазон_критериев: Диапазон ячеек, которые вы хотите сравнить с критериями;
• критерий1: Первый критерий, используемый для определения, какие ячейки следует суммировать;
• критерий2: Второй критерий, используемый для определения, какие ячейки следует суммировать;
• диапазон_суммирования: Диапазон ячеек, которые вы хотите суммировать.

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

Объяснение формулы:

=СУММЕСЛИ(B4:B14;"John";C4:C14) + СУММЕСЛИ(B4:B14;"David";C4:C14)

• Первая часть СУММЕСЛИ(B4:B14;"John";C4:C14) находит строки Джона и суммирует общее количество;
• Вторая часть СУММЕСЛИ(B4:B14;"David";C4:C14) находит строки Дэвида и суммирует общее количество;
• Затем эти две формулы СУММЕСЛИ складываются, чтобы получить общее количество товаров, поставленных как Джоном, так и Дэвидом.

Используя функции СУММ и СУММЕСЛИ

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

Общая формула с жестко заданным текстом:

• диапазон_критериев: Диапазон ячеек, которые вы хотите сравнить с критериями;
• критерий1: Первый критерий, используемый для определения, какие ячейки следует суммировать;
• критерий2: Второй критерий, используемый для определения, какие ячейки следует суммировать;
• диапазон_суммирования: Диапазон ячеек, которые вы хотите суммировать.

Общая формула со ссылками на ячейки:

• диапазон_критериев: Диапазон ячеек, которые вы хотите сравнить с критериями;
• ячейки_критериев: Ячейки, содержащие критерии, которые вы хотите использовать;
• диапазон_суммирования: Диапазон ячеек, которые вы хотите суммировать.

Пожалуйста, введите или скопируйте одну из приведенных ниже формул в пустую ячейку, а затем получите результат:

Объяснение формулы:

=СУММ(СУММЕСЛИ(B4:B14; {"John";"David"}; C4:C14))

> СУММЕСЛИ(B4:B14; {"John";"David"}; C4:C14):

• {"John";"David"}: Константа массива, представляющая собой набор множественных критериев, указанных в фигурных скобках.
• СУММЕСЛИ(B4:B14;"David";C4:C14) Константа массива с использованием логики ИЛИ заставляет функцию СУММЕСЛИ суммировать числа в C4:C14 на основе одного из нескольких критериев ("John" и "David"), и она вернет два отдельных результата: {410;1520}.

> СУММ(СУММЕСЛИ(B4:B14; {"John";"David"}; C4:C14))=СУММ({410;1520}): Наконец, эта функция СУММ складывает результаты массива, чтобы вернуть результат: 1930.

Суммирование с несколькими критериями на основе логики И с использованием функции СУММЕСЛИМН

Если вы хотите суммировать значения с несколькими критериями в разных столбцах, вы можете использовать функцию СУММЕСЛИМН для быстрого решения этой задачи. Общий синтаксис следующий:

• диапазон_суммирования: Диапазон ячеек, которые вы хотите суммировать;
• диапазон_критериев1: Диапазон, где применяется критерий1;
• критерий1: Первый критерий, проверяемый относительно диапазона_критериев1 и определяющий, какие ячейки следует добавлять; (тип критерия может быть: число, логическое выражение, ссылка на ячейку, текст, дата или другая функция Excel.)
• диапазон_критериев2, критерий2…: Другие дополнительные диапазоны и их связанные критерии. (в формуле СУММЕСЛИМН можно установить до 127 пар диапазон_критериев и критериев.)

Допустим, у меня есть таблица с колонками Продукт, Поставщик, Количество и Цена, как показано на скриншоте ниже. Теперь я хочу найти сумму общей цены продукта «Apple» и количества, которое больше или равно 200.

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

Объяснение формулы:

=СУММЕСЛИМН(D4:D14;A4:A14;"Apple";C4:C14;">=200")

• Диапазон A4:A14 проверяет продукт «Apple», а диапазон C4:C14 извлекает все количества, большие или равные 200, затем функция СУММЕСЛИМН суммирует только совпадающие ячейки из диапазона D4:D14.

Связанные функции:

Больше статей: