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))
Если ваши условия должны быть введены в отдельные ячейки, вы можете ввести подстановочные знаки непосредственно в эти ячейки и предоставить ссылку на диапазон в качестве критерия для формулы СУММПРОИЗВ СУММЕСЛИ:
В этом примере мы поместили подстановочный знак перед именами элементов, чтобы соответствовать любой предшествующей последовательности символов, например Зеленые яблоки а такжеБананы Голдфингер
. Чтобы получить общее количество элементов, содержащих определенный текст в любом месте ячейки, поместите звездочку с обеих сторон, например, «*яблоко*».
Вот как использовать СУММЕСЛИ в Excel с несколькими условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания SUMIF несколько критериев
(файл .xlsx)
Вас также могут заинтересовать