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

В этой статье вы найдете:

  • Подробные инструкции по работе с СУММЕСЛИМН.
  • Примеры использования функции.
  • Советы и рекомендации.

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

Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что преобразование ее в СУММЕСЛИМН потребует лишь букв "МН" и некоторого количества дополнительных критериев. Это может показаться вполне логичным… но «логично» - это то, что не всегда имеет место при работе с Microsoft:)

Как работает функция СУММЕСЛИМН?

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

По сравнению с СУММЕСЛИ, здесь синтаксис немного сложнее:

СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)

Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.

диапазон_суммирования - нужно указать диапазон для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.

диапазон_условия1 - обязательный первый диапазон ячеек, который мы будем проверять на соответствие условию.

условие1 - обязательное первое условие, которое должно быть выполнено и по которому оценивается первый диапазон ячеек. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, "> = 10", A1, "яблоко" или СЕГОДНЯ().

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

Важно! Функция СУММЕСЛИМН в Excel работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным условиям (все требования соблюдаются для этой ячейки).

Как строится формула СУММЕСЛИМН - что нужно помнить

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

Хотя общая часть ясна - схожий пункт назначения и параметры – но различия не так очевидны, хотя и достаточно существенны.

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон сумирования является первым аргументом в СУММЕСЛИ, но только третьим в функции СУММЕСЛИМН.

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

В функции СУММЕСЛИМН в Excel он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)

2. Диапазон суммирования и область критериев должны быть одинакового размера

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

Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).

Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).

Формула СУММЕСЛИМН - пример с множеством условий

Имеются условные данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».

формула суммеслимн в Excel несколько условий

Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)

как правильно записать условия

В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.

В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.

Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.

Как видите, формула СУММЕСЛИМН обнаружила 2 совпадения, в которых был продан молочный шоколад.

Использование операторов сравнения для чисел

Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.

суммирование СУММЕСЛИМН много условий с операторами сравнения

Критерии можно записать в саму формулу СУММЕСЛИМН, и выглядеть это будет так:

=СУММЕСЛИМН(E2:E21;B2:B21;”Красный”;D2:D21;”>100”)

Но более рационально использовать ссылки на ячейки:

=СУММЕСЛИМН(E2:E21;B2:B21;I2;D2:D21;I4)

Примечание. Обратите внимание, что логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки ("").

Функция СУММЕСЛИМН работает с числами, текстом и датами точно так же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу об условном суммировании.

Сумма в интервале дат

Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших условиях, как это показано ниже.

суммеслимн в Excel пример с датами

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):

=СУММЕСЛИМН(D2:D21;A2:A21;"<="&СЕГОДНЯ();A2:A21;">="&СЕГОДНЯ()-6)

Замечание. Когда вы при составлении условия используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например "<="&СЕГОДНЯ().

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

=СУММЕСЛИ(A2:A21;">="&СЕГОДНЯ()-6;D2:D21) - СУММЕСЛИ(A2:A21;"<="&СЕГОДНЯ();D2:D21)

Однако, функция СУММЕСЛИМН в Excel сложение делает гораздо проще и понятнее, не так ли?

Суммирование по пустым и непустым ячейкам.

При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.

КритерииОписаниеСодержание
Пустые ячейки"="Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего - ни формулы, ни строки нулевой длины.=СУММЕСЛИМН(C2:C10;A2:A10;"=";B2:B10,"=")
Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты.
«»Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = "").=СУММЕСЛИМН(C2:C10;A2:A10;"";B2:B10,"")  
Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками.
Непустые ячейки"<>"Суммируйте числа, соответствующие непустым, включая строки нулевой длины.=СУММЕСЛИМН(C2:C10;A2:A10;"<>";B2:B10,"<>") Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками.
Суммируйте числа, соответствующие непустым, не включая строки нулевой длины.=СУММ(C2:C10)-СУММЕСЛИМН(C2:C10;A2:A10;"";B2:B10,"")
или
{=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))}
Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно!

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

СУММЕСЛИМН с «пустыми» и «непустыми» ячейками

По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.

=СУММЕСЛИМН(D2:D21;F2:F21;"";B2:B21;"Красный")

или

=СУММЕСЛИМН(D2:D21;F2:F21;"=";B2:B21;"Красный")

Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.

Несколько условий И и ИЛИ в СУММЕСЛИМН

А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.

пример суммы нескольких условий для двух товаров

Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.

Поэтому первый вариант расчета таков:

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)

Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.

Второй вариант: используем элемент массива критериев и функцию СУММ.

=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{"*молочный*";"*черный*"};F2:F21;I4))

Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.

Альтернативный пример суммы с условиями – СУММПРОИЗВ.

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

сумма по двум условиям - формулы СУММЕСЛИМН и СУММПРОИЗВ

Разберем подробнее, как работает СУММПРОИЗВ():

=СУММПРОИЗВ(--(B2:B21=$I$12);--(D2:D21>I13);E2:E21)

Результатом вычисления B2:B21=$I$12 является массив

{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.

ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.

А что за странные знаки «минус» перед этими выражениями? Дело в том, что нам необходимы не эти логические выражения, а числа, чтобы их затем можно было перемножать и складывать. Если Эксель производит математическую операцию с логическим выражением, то он автоматически преобразует его в число. А знак минус означает умножение на -1. А если дважды умножить на -1, то число в результате не изменится. Это мы помним еще из школьной математики 😊

И в результате логический массив превратится в массив чисел {0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0}.

Результатом вычисления D2:D21>I13 является массив

{ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.

ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.

И, наконец, результатом вычисления В2:В13 является массив {11250:23210:12960:3150:5280:9750:3690:18300:5720:6150: 8400:2160:7200:1890:17050:3450:15840:2250:7200:8250}, т.е. просто числа из столбца E.

Результатом поэлементного умножения этих трех массивов является {0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0}. Суммируем эти произведения и получаем 39050.

Формула массива для суммы по условиям

И еще один вариант расчета – применим формулу массива. В I14 запишем:

=СУММ((B2:B21=I12)*(D2:D21>I13)*(E2:E21))

Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.

Как найти сумму без формул при помощи автофильтра Excel

Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу Excel. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.

автофильтр и сумма с условиями в таблице

Без всяких формул итог по отфильтрованным строкам будет определён.

Итак, теперь найти сумму значений по нескольким условиям в Excel при помощи СУММЕСЛИМН или другими способами не будет для выс проблемой.