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

Знаете ли вы, как суммировать числа в определенном столбце, когда значение в другом столбце удовлетворяет любому из указанных условий? В этой статье вы узнаете 3 разных способа СУММЕСЛИ, используя несколько критериев и логику ИЛИ.

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

СУММЕСЛИ + СУММЕСЛИ для суммирования ячеек, равных тому или иному

Когда вы хотите суммировать числа в одном столбце, когда другой столбец равен либо A, либо B, наиболее очевидным решением является обработка каждого условия по отдельности, а затем сложение результатов вместе:

СУММЕСЛИ(диапазон, критерии1диапазон_суммы) + СУММЕСЛИ(диапазон, критерии2сумма_диапазон)

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

=СУММЕСЛИ(A2:A10, «яблоки», B2:B10) + СУММЕСЛИ(A2:A10, «лимоны», B2:B10)

Или вы можете ввести критерии в отдельные ячейки и ссылаться на эти ячейки:

=СУММЕСЛИ(A2:A10, E1, B2:B10) + СУММЕСЛИ(A2:A10, E2, B2:B10)

Где A2:A10 — список элементов (диапазон), B2:B10 — числа для суммирования (sum_rage), E1 и E2 — целевые элементы (критерии):

Как работает эта формула:

Первая функция СУММЕСЛИ складывает яблоки продаж, второй СУММЕСЛИ суммирует Лимоны продажи. Операция сложения суммирует промежуточные итоги и выводит итог.

СУММЕСЛИ с константой массива — компактная формула с несколькими критериями

Подход SUMIF + SUMIF отлично работает для двух условий. Если вам нужно суммировать с 3 или более критериями, формула станет слишком большой и трудной для чтения. Чтобы получить тот же результат с помощью более компактной формулы, укажите критерии в виде константы массива:

СУММ(СУММЕСЛИ(диапазон, {критерии1, критерии2, критерии3…}, диапазон_суммы))

Пожалуйста, помните, что эта формула работает на основе логики ИЛИ — ячейка суммируется, когда выполняется какое-либо одно условие.

В нашем случае, чтобы суммировать продажи для 3 различных товаров, формула выглядит следующим образом:

=СУММ(СУММЕСЛИ(A2:A10, {«Яблоки»,»Лимоны»,»Апельсины»}, B2:B10))

На приведенном выше снимке экрана условия жестко закодированы в массиве, что означает, что вам придется обновлять формулу при каждом изменении критериев. Чтобы избежать этого, вы можете ввести критерии в предопределенные ячейки и указать формулу в качестве ссылки на диапазон (E1:E3 в этом примере).

=СУММ(СУММЕСЛИ(A2:A10, E1:E3, B2:B10))

В Excel 365, который поддерживает динамические массивы, он работает как обычная формула, завершаемая клавишей Enter. В преддинамических версиях Excel 2019, Excel 2016, Excel 2013 и более ранних его следует вводить как формулу массива с помощью сочетания клавиш Ctrl + Shift + Enter:

Как работает эта формула:

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

{425;425;565}

Чтобы получить итог, мы используем функцию СУММ и оборачиваем ее формулой СУММЕСЛИ.

СУММПРОИЗВ и СУММЕСЛИ для суммирования ячеек с несколькими условиями ИЛИ

Не любите массивы и ищете нормальную формулу, которая позволяла бы суммировать по нескольким критериям в разных ячейках? Без проблем. Вместо СУММ используйте функцию СУММПРОИЗВ, которая изначально обрабатывает массивы:

СУММПРОИЗВ(СУММЕСЛИ(диапазон, crireria_rangeсумма_диапазон))

Предполагая, что условия находятся в ячейках E1, E2 и E3, формула принимает следующий вид:

=СУММПРОИЗВ(СУММЕСЛИ(A2:A10, E1:E3, B2:B10))

Как работает эта формула:

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

СУММЕСЛИ с использованием нескольких критериев с подстановочными знаками

Поскольку функция СУММЕСЛИ в Excel поддерживает подстановочные знаки, при необходимости их можно включить в несколько критериев.

Например, для суммирования продаж по всем видам яблоки а также Бананыформула:

=СУММ(СУММЕСЛИ(A2:A10, {«*яблоки»,»*бананы»}, B2:B10))

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

В этом примере мы поместили подстановочный знак перед именами элементов, чтобы соответствовать любой предшествующей последовательности символов, например Зеленые яблоки а такжеБананы Голдфингер

. Чтобы получить общее количество элементов, содержащих определенный текст в любом месте ячейки, поместите звездочку с обеих сторон, например, «*яблоко*».