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

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

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

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

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

С помощью формулы СУММЕСЛИ + СУММЕСЛИ +…

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

• criteria_range: Диапазон ячеек, в котором необходимо найти совпадения по критериям;
• criteria1: Первый критерий, используемый для определения ячеек, которые следует просуммировать;
• criteria2: Второй критерий, используемый для определения ячеек, которые следует просуммировать;
• sum_rangeДиапазон ячеек, по которому требуется выполнить суммирование.

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

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

=SUMIF(B4:B14,«John»,C4:C14) + SUMIF(B4:B14,«David»,C4:C14)

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

С использованием функций СУММ и СУММЕСЛИ

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

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

• criteria_range: Диапазон ячеек, в котором необходимо найти совпадения по критериям;
• criteria1: Первый критерий, используемый для определения ячеек, которые следует просуммировать;
• criteria2: Второй критерий, используемый для определения ячеек, которые следует просуммировать;
• sum_range: Диапазон ячеек, из которого необходимо выполнить суммирование.

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

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

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

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

=SUM(SUMIF(B4:B14, {«John»,«David»}, C4:C14))

> SUMIF(B4:B14, {«John»,«David»}, C4:C14):

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

> SUM(SUMIF(B4:B14, {«John»,«David»}, C4:C14))=SUM({410,1520}): Наконец, функция СУММ складывает элементы этого массива и возвращает итоговое значение — 1930.

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

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

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

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

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

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

=SUMIFS(D4:D14,A4:A14,«Apple»,C4:C14,«>,=200»)

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

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

Дополнительные статьи: